Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
melissapluke
Partner
Partner

Opposite of a Cross table

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).

melissapluke_0-1614640614889.png

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

melissapluke_1-1614640690757.png

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

 

1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

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.

View solution in original post

9 Replies
GaryGiles
Specialist
Specialist

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.

View solution in original post

saran7de
Master
Master

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

melissapluke
Partner
Partner
Author

Here you go.

saran7de
Master
Master

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;
saran7de
Master
Master

Output:

commQV25.PNG

melissapluke
Partner
Partner
Author

Hi,

Both solutions worked, thank you both!

Melissa

saran7de
Master
Master

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;
saran7de
Master
Master

Output:

commQV26.PNG

johngouws
Partner
Partner

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...