Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey guys,
I wonder if I can easily do this in Qlikview? I know I can do it in SQL but it's a real pain.
I have a dataset whereby one field contains a 1:M delimited set of values, e.g:
IndicatorID
|01|02|
|01|02|03|
|02|04
And I have a dim/lookup which contains a unique value for each possible value:
IndicatorA, 01
IndicatorB, 02...
Is there a way in Qlikview I can join/map these values back to the dimension so that I can display the dimension value as a separate listbox?
e.g.
Listbox1 (IndicatorA)
Listbox2 (IndicatorB) etc...?
Thanks
Immy
Not sure exactly what you want, but I can see a couple of paths you should explore. The first one is to use the SubField() function. With the following script you will get all IDs in one field:
Data:
Load * Where Len(Trim(IndicatorID))>0;
Load
IndicatorID as IndicatorIDs,
Subfield(IndicatorID,'|') as IndicatorID,
RecNo() as RecNo
Inline
[IndicatorID
|01|02|
|01|02|03|
|02|04];
Dim:
Load * Inline
[Dim, IndicatorID
IndicatorA, 01
IndicatorB, 02
IndicatorC, 03];
HIC
... and the second function you should explore is the Generic prefix:
Data:
Load * Where Len(Trim(IndicatorID))>0;
Load
IndicatorID as IndicatorIDs,
Subfield(IndicatorID,'|') as IndicatorID,
RecNo() as RecNo
Inline
[IndicatorID
|01|02|
|01|02|03|
|02|04];
Join
Load * Inline
[Dim, IndicatorID
IndicatorA, 01
IndicatorB, 02
IndicatorC, 03];
Generic Load RecNo, Dim, IndicatorIDs Resident Data;
But you probably need additional data to make use of the Generic prefix...
HIC
Yes, I realize my explanation isn't the best, Henric - apologies.
Let me try your subfield approach and I'll post back.
many thanks
Immy
Hi Henric,
Very elegant solution. I have a problem which is a bit more complicated. For each value in the IDNumber field, I need to generate additional rows in the table with duplicate values of all the other fields.
My data looks like this:
IDNumber Activity ActivityPriority Date
1,5,6 Description1 High 10/7/2017
9,8,10,15,16 Description2 Low 9/6/2016
12,4 Description3 Medium 5/11/2015
7 Description4 Very High 11/4/2015
For each value in the IDNumber field, I need a separate row in the table with the other 3 fields duplicated:
IDNumber Activity Activity Priority Date
1 Description1 High 10/7/2017
5 Description1 High 10/7/2017
6 Description1 High 10/7/2017
9 Description2 Low 9/6/2016
8 Description2 Low 9/6/2016
10 Description2 Low 9/6/2016
15 Description2 Low 9/6/2016
16 Description2 Low 9/6/2016
12 Description3 Medium 5/11/2015
4 Description3 Medium 5/11/2015
7 Description4 Very High 11/4/2015
Any ideas would be greatly appreciated.
many thanks,
Pam
This is straighforward with SubField():
Load
Subfield(IDNumber,',') as IDNumber,
Activity,
Dual(ActivityPriority,Match(ActivityPriority,'Very High','High','Medium','Low')) as ActivityPriority,
Date#(Date,'M/D/YYYY') as Date
Inline
[IDNumber,Activity,ActivityPriority,Date
"1,5,6",Description1,High,10/7/2017
"9,8,10,15,16",Description2,Low,9/6/2016
"12,4", Description3,Medium,5/11/2015
7,Description4,Very High, 11/4/2015];
HIC
Wow, Thanks HIC.
That did exactly what I needed and I learned something new.
I had not used Subfield without the third parameter in a load to get such an effect before.
Many thanks,
Pam