Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There
I have following requirement, I am getting values from Database as Old code, in Qlikview script I want to change these values to new code as below.
is there any simple way to achieve this?
Old Code | New Code |
0820 | 3305 |
0821 | 3306 |
0822 | 3307 |
0823 | 3308 |
0824 | 3388 |
0825 | 3310 |
0826 | 3311 |
0827 | 3356 |
0828 | 3313 |
0829 | 3366 |
0830 | 3315 |
0831 | 3387 |
thanks in advance
You need to link the two columns first. Then you can use Applymap(). See Data Cleansing
HIC
May be use the Mapping Load combined with ApplyMap():
MappingTable:
Mapping
LOAD [Old Code],
[New Code]
From Source;
FactTable:
LOAD
ApplyMap('MappingTable', Code) as NewCode
FROM Source;
DROP field Code from FactTable;
Rename Field NewCode to Code;
Map_Table:
Mapping Load OldValue, NewValue From TableName;
Final:
Load Field1, Field2, ApplyMap('Map_Table',OldValue,Null() as NewValue From TableName;
Hi Sunny
Thanks for the updates, I have situation like below
QVD1 has - Old Code
QVD2 has - New Code
Based on that when fetching in QVW need to show New Code.
What other columns are there in each of the QVD? how would we know which code in QVD1 linked to which code in QVD2?
QVD 1 and QVD 2 is joined with attribute named Code.
Load
Code, // Old code 820 821 822 823 824 825 826 827 828 829 830 831
Name ,
City ,
Country
from QVD 1.
Join
Load
Code, // New Code 3305 3306 3307 3308 3388 3310 3311 3356 3313 3366 3315 3387
Name ,
City ,
Country
from QVD 2.
QVD 1 and QVD 2 has other matching records around 300+ codes.
But in QVD 2 new codes are present which we need to replace from QVD 1.
You need to link the two columns first. Then you can use Applymap(). See Data Cleansing
HIC