Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

wonkymeister
Contributor

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

Re: join/mapping issue

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

2 Replies
Not applicable

Re: join/mapping issue

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

MVP
MVP

Re: join/mapping issue

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.

Community Browser