Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wonkymeister
Creator III
Creator III

join/mapping issue

Hi,

I have two datasets, 1 old and 1 new which I have joined via the PK.

Both contain 2 columns, Sub_Code and  Sub_Name

Problem is the Sub_Code/Sub_Names in the new dataset have been aggregated (there are more subjects in the old dataset, fewer in the new dataset) – and therefore I need to map the two so that when something is selected in one dataset the relevant is selected in the other dataset.

Ie.

In the old dataset:

Sub_Code                                           Sub_Name                      

1                                                         Soup                                   

2                                                         Bread                                 

3                                                         Cheese                              

4                                                         Water

In the new dataset:

Sub_Code                                           Sub_Name

1                                                         Food

2                                                         Beverage

So if Sub_Code 1 was selected in the new dataset, Sub_Code’s 1,2 & 3 would be displayed from the old dataset.

Any ideas? I thought of creating a key or something via an inline load?

1 Solution

Accepted Solutions
Not applicable

As I understand your question correctly, the new data set have different sub code names than old data set and more like old data set sub code category.

Please create the a Mapping table either Inline or Excel , to add new sub code called Sub Code Category.

Map_Category:

LOAD * INLINE [

OLD_SUB_CODE , NEW_SUB_CODE

Soup ,  Food

Chesee , Food

Bread ,  Food

Water , Beverage

.....

] ;

LOAD

     Sub_Code_ID ,

     Sub_Code_Name ,

     ApplyMap( 'Map_Category' , Sub_Code_Name  , null() ) AS Sub_Code_Category

From Source.qvd (qvd) ;

View solution in original post

2 Replies
Not applicable

As I understand your question correctly, the new data set have different sub code names than old data set and more like old data set sub code category.

Please create the a Mapping table either Inline or Excel , to add new sub code called Sub Code Category.

Map_Category:

LOAD * INLINE [

OLD_SUB_CODE , NEW_SUB_CODE

Soup ,  Food

Chesee , Food

Bread ,  Food

Water , Beverage

.....

] ;

LOAD

     Sub_Code_ID ,

     Sub_Code_Name ,

     ApplyMap( 'Map_Category' , Sub_Code_Name  , null() ) AS Sub_Code_Category

From Source.qvd (qvd) ;

jagan
Luminary Alumni
Luminary Alumni

Hi,

There should be some mapping between the old and new data sets, like below

In the old dataset:

Sub_Code          Old_Sub_Code                                           Sub_Name                     

1                                   1                                                         Soup                                  

1                                   2                                                         Bread                                

1                                   3                                                         Cheese                             

2                                   4                                                         Water

In the new dataset:

Sub_Code                                           Sub_Name

1                                                         Food

2                                                         Beverage

Do this in Excel file, if there is any changes then just updating the Excel is easier instead of changing in Inline script.

Hope this helps you.

Regards,

Jagan.