Can I join concatenated field value onto a dimension?

[imtiaz_ullah]

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

[hic]

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

[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

[imtiaz_ullah]

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

[pamelabremer]

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

[hic]

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

[pamelabremer]

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