Opposite of a Cross table

[melissapluke]

Hi all,

I have a data table which has "Cases" and within the cases are multiple rows. I need to condense those multiple rows into a single row. Here is a very small sample of the data. 2 cases, with 3 rows each. (other cases have more rows, it is variable).

Based on the E&MNo column, I need to create columns for the procedures code/visit IDs that look like this:

Some cases have a lot of rows (max 30ish) but I am trying to avoid having to do 30 loads where you isolate the E&MNo=1, then E&MNo=2 etc.

Any ideas?

Thanks,

Melissa

[GaryGiles · Accepted solution]

Try the following in your load script (I assumed that your table is named Cases).  I'm sure there is a more eloquent way to derive the ordinals '1st','2nd','3rd', etc. from the counter, but this was quick :

temp_maxlevel:
LOAD max([E&MNo]) as maxNo
resident Cases;

let maxlevel = Peek('maxNo',0,'temp_maxlevel');

DROP TABLE temp_maxlevel;

NewCases:
Load distinct [Case ID]
Resident Cases;

for i = 1 to maxlevel
 Let k = $(i)&if(Match(Right('$(i)',2),1,01,21,31,41,51,61,71,81,91)>0,'st',
                         if(Match(Right('$(i)',2),2,02,22,32,42,52,62,72,82,92)>0,'nd',
                         if(Match(Right('$(i)',2),3,03,23,33,43,53,63,73,83,03)>0,'rd','th')));

    Join (NewCases)
    Load [Case ID],
               [Procedure Code] as [$(k) Procedure Code],
               [Original Visit Type ID] as [$(k) Original Visit Type ID]
    Resident Cases
    where [E&MNo] = $(i);
next

drop table Cases;

Hope this helps.

[Saravanan_Desingh]

Can you upload the Excel sheet which you have shown above?

[melissapluke]

Here you go.

[Saravanan_Desingh]

Try this,

tab1:
LOAD * INLINE [
    Case ID, Procedure Code, Original Visit ID, E&Mno
    8910, 99395, 49, 1
    8910, 99204, 1, 2
    8910, 99214, 2, 3
    9867, 99233, 7, 1
    9867, 99356, 26, 2
    9867, 99357, 27, 3
];

tab2:
Generic
LOAD [Case ID],[E&Mno]&' Procedure Code',[Procedure Code]
Resident tab1;
Generic
LOAD [Case ID],[E&Mno]&' Original Visit ID',[Original Visit ID]
Resident tab1;

Drop Table tab1;

[Saravanan_Desingh]

Output:

[melissapluke]

Hi,

Both solutions worked, thank you both!

Melissa

[Saravanan_Desingh]

You can add st, nd & th like this.

tab1:
LOAD *, Pick(WildMatch([E&Mno],
            1,21,31,
            2,22,
            3,23,
            '*'),
            'st ', 'st ', 'st ',
            'nd ', 'nd ',
            'rd ', 'rd ',
            'th '
    ) As [E&Mno2];
LOAD * INLINE [
    Case ID, Procedure Code, Original Visit ID, E&Mno
    8910, 99395, 49, 1
    8910, 99204, 1, 2
    8910, 99214, 2, 3
    9867, 99233, 7, 1
    9867, 99356, 26, 2
    9867, 99357, 27, 3
];

tab2:
Generic
LOAD [Case ID],[E&Mno]&' '&[E&Mno2]&' Procedure Code',[Procedure Code]
Resident tab1;
Generic
LOAD [Case ID],[E&Mno]&' '&[E&Mno2]&' Original Visit ID',[Original Visit ID]
Resident tab1;

Drop Table tab1;

[Saravanan_Desingh]

Output:

[johngouws]

Good morning. 
I needed to do something similar with finding how many Supplier have supplied certain Product. The data is not quite the same as your but may give you a idea. 

For my solution @sunny_talwar was able to help. Look at this post.
https://community.qlik.com/t5/New-to-Qlik-Sense/Create-Transposed-table-during-load-process/td-p/163...