topic Re: transpose the table in App Development

transpose the table

rushi1093 — Fri, 29 Sep 2023 10:55:51 GMT

I want transpose the table's column into separate entries?

for ex.

Perm ID	Code	Count	Code	Count
abc	kyubis1	24	kyubis2	33
def	kyubij1	19	krsfgd2	46

Can i convert this to :

Perm ID	Code	Count
abc	kyubis1	24
abc	kyubis2	33
def	kyubij1	19
def	krsfgd2	46

just to have separate and distinct data instead of horizontal repeatation.

Please help

Re: transpose the table

rubenmarin — Sat, 30 Sep 2023 06:59:00 GMT

Hi, the first table has some columns with the same name, I don't know how you can refer to the second 'Code' column. I think your real data might be different and it will be better to have an example of that real structure example.

If it helps: to split a row with separators in different rows you can use subfield() fucntion with only the 2 first parameters. (2nd example here)

There is also the option to load using crosstable

Re: transpose the table

rushi1093 — Sun, 01 Oct 2023 18:45:44 GMT

Yes I agree columns are different. Actually its coming from text file which has RS and GS to separate the code and count..basically the table is getting populated horizontally but I require the data vertically as shown in the solution table. So that every occurance of code and count will be appended and not extend the columns but rows..kindly check if this helps else i will try to share the text file of data.

Re: transpose the table

rubenmarin — Mon, 02 Oct 2023 06:49:21 GMT

Hi, for the second parameter of subfield you can use Chr() and set the code for RS and GS, maybe you need to do it in some steps: first to split each pair, and a second one to split each pair value.

Re: transpose the table

rushi1093 — Tue, 03 Oct 2023 08:14:54 GMT

Pfa for the actual text file. @rubenmarin @jonashertz …in this as you see, RS and GS prefix data gets populated horizontally..I required only count and code column for all such entries.

Re: transpose the table

rubenmarin — Tue, 03 Oct 2023 08:19:44 GMT

Hi, it will be better if you attach some sample data in a format that we can test to load in Qlik.