Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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) ;
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) ;
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.