convert rows in to columns (without generic load)

[beck_bakytbek]

Hi Folks,

i got a question:

my table does look like:

Material	Months	Value
A	1	5
A	2	10
B	1	2
B	2	4

my expected output is:

Material	1	2
A	5	10
B	2	4

My question is: is that possible to convert rows to columns without using of generic load?

Does anybody have any idea?

Thanks a lot

[marcus_sommer]

It might be done with multiple loads, maybe:

for i = 1 to 12
   let var = if($(i) = 1, 't:', 'join(t)');
   $(var) load Material, Value as $(i) from xyz where Months = $(i);
next

Depending on the real scenario a join within or without a loop might be tricky and n mappings may more suitable. Thinkable are further string-aggregations which are within a following load per subfield() splitted again.

Neither the generic load nor any alternatives will be trivial and less performant compared with a normal table-structure. Not thinking of the difficulties which it produced to handle and access such cross-table within the UI. Therefore such approach should be only done if there is really no alternatives to get the wanted views.

- Marcus

[beck_bakytbek]

Hi Marcus,

thanks a lot for your feedback, i tried to implement it in my scriptm but it does not work.

[marcus_sommer]

Like mentioned it's not trivial and usually such an approach has often more disadvantages as benefits. Therefore I suggest to consider it very carefully if there is no other way to get the wanted views.

Beside this don't try to apply the ideas from above directly within your application else use a dummy-app with such simplified test-data like in your example and try it there. If it worked and you could comprehend it you may add step by step some more fields/tables in the direction of your origin data-model and only if this worked you should adapt it in the final app.

- Marcus

[beck_bakytbek]

Hello Marcus, thanks a lot for your reply, this is exactly how i did it, and in my dummy app this approach did not work.

[marcus_sommer]

What happens - nothing, any errors, wrong/unexpected results and where?

- Marcus

[beck_bakytbek]

during script execution the error message appears

[marcus_sommer]

What for an error?

[beck_bakytbek]

Hello Marcus, sorry for my late reply, when i use this code within my dummy app, i receive this error:

i checked my code and can not find the deviation

[marcus_sommer]

Here are some slight adjustments - just to exclude some potential difficulties with the variable-handling:

for i = 1 to 12
   let var = if($(i) = 1, 't:', 'join(t)');
   $(var) load Material, Value as [$(i)] from xyz where Months = '$(i)';
next

whereby the error-messages hint to an opening-error of the file regardless to the other stuff within the load-statement. Therefore check the path again (just use the wizard to access this file and then copy the path-part).

- Marcus