Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Can you upload the Excel sheet which you have shown above?
Here you go.
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;
Output:
Hi,
Both solutions worked, thank you both!
Melissa
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;
Output:
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...