Table Transformer Use "while" to create columns

[Kai Winter]

Hello everyone! 

I am learning how to use the Table Transformer SQL feature. I see "WHILE" in a list of available keywords, but I can't find any of its usages, only this link. Could someone please give me an example of how to apply it correctly? 

My use case

I have a table with numbers. OrderedStuff can be integer or string, other columns are float. OrderedStuff has multiple rows with same index and it should be this way. 

OrderedStuff | Val1           | Val2           | Val3 | ...

---

15                    | 1.23           | 5454.65       | 34

15                    |  2324         |   123.6         | 56.9

---

14                    | 23.565       | 43242.3       | 87

---

13                    | 87.5           | 13.67           | 56

13                    |18.5            | 12.43           | 54

---

... 

I would like to create a separate column with ValX for each OrderedStuff.

Result I would like to have

Val1 15| Val2 15  | Val3 15| Val1 14| Val2 14 | Val3 14| Val1 13     | Val2 13      | Val313

---

1.23      | 5454.65  | 34         | 23.565  | 43242.3 | 87         | 87.5           | 13.67          | 56

---

2324     |   123.6   | 56.9 |                                                  |18.5            | 12.43         | 54

---

...

The empty space usually says "false" in my case which is okay.

Right now I'm doing it this way

SELECT

CASE WHEN 'OrderedStuff' = 15 THEN 'Val1' END AS 'Val1 15',
CASE WHEN 'OrderedStuff' = 15 THEN 'Val2' END AS 'Val2 15'
CASE WHEN 'OrderedStuff' = 15 THEN 'Val3' END AS 'Val3 15',

CASE WHEN 'OrderedStuff' = 14 THEN 'Val1' END AS 'Val1 14',
CASE WHEN 'OrderedStuff' = 14 THEN 'Val2' END AS 'Val2 14',
CASE WHEN 'OrderedStuff' = 14 THEN 'Val3' END AS 'Val2 14',

CASE WHEN 'OrderedStuff' = 13 THEN 'Val1' END AS 'Val1 13',
CASE WHEN 'OrderedStuff' = 13 THEN 'Val2' END AS 'Val2 13'
CASE WHEN 'OrderedStuff' = 13 THEN 'Val3' END AS 'Val3 13'

FROM T1

The problem is: I need to write a general script, that would work with any integer number of OrderedStuff and would create separate columns for each 'ValX integer number'. 

I imagine a solution like

WHILE i from 0 to LENGTH( OrderedStuff ) 

DO { 

WHILE X from 0 to LENGTH( ValX )

DO {

SELECT 

CASE WHEN 'OrderedStuff' = i THEN 'ValX' END AS 'ValuesX  i'

FROM T1
}}  

 Is it possible to do it in Table Transformer?

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Kai Winter ,

At the moment we can't come up with a suitable solution for your case regarding that you may have a different number of ValX columns in the source table.

We understand your idea with a schematic solution, but the Table Transformer macro is based on the AlaSQL library so now we don't see how to implement it in practice. If anything comes to us later, we'll return to this thread.

As for now you may also try to use the Pivot Table macro to aggregate your data:

If you are not sure how many ValX you have, then type in manually all the possible variants beforehand in the "Calculated column" section.

Hope this solution somehow helps.

[Katerina Kovriga _Stiltsoft_]

And you may also try the following SQL query instead of your original one:

SELECT T2.'OrderedStuff', CONCAT_VIEW_AGGR(FORMATWIKI(T2.'Value' + " \n")) AS 'Value'
FROM (SELECT ("val1 " + T1.'OrderedStuff') AS 'OrderedStuff', T1.'Val1' AS 'Value' FROM T1
UNION SELECT ("val2 " + T1.'OrderedStuff'), T1.'Val2' FROM T1
UNION SELECT ("val3 " + T1.'OrderedStuff'), T1.'Val3' FROM T1) AS T2
GROUP BY T2.'OrderedStuff' ORDER BY T2.'OrderedStuff'

It seems to be a little bit shorter, but the valX columns are still listed manually.

[Kai Winter]

Hi, @Katerina Kovriga _Stiltsoft_!

Thank you very much for the quick response! I learned a lot while playing with your suggested solution. It's a great way to generate multiple columns simultaneously.

However, there are a couple of problems I have with this solution:

• My table has other columns like 'Date' and a lot of empty cells, so I am not sure if I can use aggregate functions without mixing things up.
• I would like to make a timeline plot of values from the resulting table, so I need to have one value per cell only, otherwise, Chart from Table macro won't accept it.

Do you know if this is possible to implement?

It worked with my "CASE WHEN" army, but I am at risk of public shaming from my fellow physicists for being "unelegant" :) 

P.S. Is there a page where I could find examples with keywords "WHILE" or "WITH"?

[Katerina Kovriga _Stiltsoft_]

Unfortunately, we also didn't come with a better solution than yours. Indeed our examples may be shorter but they aggregate numbers and place them into one cell (not separate).

What concerns the examples, it seems that we don't have any use cases with the mentioned keywords. The cases usually come from our customers, if the tasks are popular, we add them to our documentation.

The Table Transformer macro itself is based on the AlaSQL library, you may look into its options.

If you have other cases, please create a question here or refer to our support.

[Kai Winter]

Thank you for your detailed answers @Katerina Kovriga _Stiltsoft_. I'll try to figure out how to use AlaSQL ;)