topic Re: One off field value change in script in QlikView

One off field value change in script

Sun, 27 Sep 2015 20:00:02 GMT

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

Re: One off field value change in script

swuehl — Sun, 27 Sep 2015 20:18:00 GMT

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.

Re: One off field value change in script

MarcoWedel — Sun, 27 Sep 2015 21:39:19 GMT

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

Re: One off field value change in script

Sun, 27 Sep 2015 21:41:21 GMT

Thanks Marco - this is also a great solution.

Re: One off field value change in script

MarcoWedel — Sun, 27 Sep 2015 21:55:00 GMT

You're welcome

regards

Marco