topic Re: join/mapping issue in QlikView

join/mapping issue

wonkymeister — Wed, 10 Dec 2014 14:43:02 GMT

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?

Re: join/mapping issue

Thu, 11 Dec 2014 01:19:00 GMT

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

Re: join/mapping issue

jagan — Thu, 11 Dec 2014 02:19:46 GMT

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.