One off field value change in script

[#1]

Hi guys

Attached is a sample dataset. What I need to do is change the values ( highlighted in yellow ) ie field S1 and ASIA 1 for Company 'Westin' from 'IT&Telecoms' and 'Software' respectively both both to 'Networks' instead. I need to do this in script.

Many Thanks

[swuehl · Accepted solution]

You can start with a  condition in the LOAD script:

LOAD [Company Code],

     [company name],

     Date,

     Sales,

     if([company name]='Westin','Networks', S1) as S1,

     S2,

     S21,

     S4,

     if([company name]='Westin','Networks', [ASIA 1]) as [ASIA 1]

FROM

[.\sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

You can also look into a MAPPING approach, especially when there is more than one company to handle.

[MarcoWedel]

Hi,

one solution might be:

mapChangeValues:
Mapping
LOAD Hash128([company name], ChangeField, OldValue),
    NewValue
INLINE [
    company name, ChangeField, OldValue, NewValue
    Westin, S1, IT & Telecoms, Networks
    Westin, ASIA 1, Software, Networks
];

table1:
LOAD [Company Code],
    [company name],
    Date,
    Sales,
    ApplyMap('mapChangeValues',Hash128([company name],'S1',S1),S1) as S1,
    S2,
    S21,
    S4,
    ApplyMap('mapChangeValues',Hash128([company name],'ASIA 1',[ASIA 1]),[ASIA 1]) as [ASIA 1]
FROM [https://community.qlik.com/servlet/JiveServlet/download/875652-187860/sample.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

[#4]

Thanks Marco - this is also a great solution.

[MarcoWedel]

You're welcome

regards

Marco