Update row value

[Gautam]

Hi All,

I am beginner to Qlikview, I have requirement. I have loaded the Sales Data from ODBC the Table Name is [SALES], One field Salesmantype Derived with Case Statement When empddress as Salesmantype. 

Now my requirement is, from the same table [SALES] I have Sales man code(SLMCODE), I have to check SLMCODE='AAA' then I have to change the value of Salesmantype ='ZZZ' like that I have to check for 5 SLMCODE's. How to achieve, plz explain with same example. 

Appreciate your response.

Thanks,

Gautam.  

[SharathPanackal]

Hi Gautam,

You can use multiple if condition statements to achieve the same.

For example, consider you have a Gender column in the data and would like to change 'M' to 'Male', 'F' to Female etc. You can run an if condition on the original column and search for values in it and perform operation if the value being searched for is found. You could save the result using alias keyword 'as' into a new column name or use the original column name to overwrite the original column values itself. Hope i made sense. Please find example script below which you can copy paste to QV.

TableName:

LOAD Employee, if(Gender='M','Male',if(Gender='F','Female','Not Filled')) as Gender;

LOAD * INLINE [
Employee, Gender
A, M
B, F
C, M
D, F
E, F
F, NF
G, M
];

[SharathPanackal]

As per your query i understand you want to change a column value based on a corresponding value in another column. Please find below an example:

TableName:

LOAD SLMCODE, SALESMANTYPE, if(SLMCODE='AAA','Type1',if(SLMCODE='BBB','Type2',if(SLMCODE='CCC','Type3',if(SLMCODE='DDD','Type4',SALESMANTYPE)))) as SALESMANTYPE_NEW;

LOAD * INLINE [
SLMCODE, SALESMANTYPE
AAA, T1
BBB, T2
CCC, T3
DDD, T4
AAA, T1
F, Type6
J, Type10
];

The above code will give the below table as output.

SLMCODE	SALESMANTYPE	SALESMANTYPE_NEW
AAA	T1	Type1
BBB	T2	Type2
CCC	T3	Type3
DDD	T4	Type4
F	Type6	Type6
J	Type10	Type10

Please mark as helpful or answered

[Gautam]

Hi Sharat,

Thanks for your reply, Here I have to change the Value based on Condition.

I have SALES table in that I have Salesmantype .

[Sales]:
LOAD
*;

SQL
SELECT s.[whsnum] as [Branch]
,[invdate] as [Invoice Date]
,[invnum] as [Invoice #]
,s.[slmcode] as [Salesman 1]

,Case when e.[empaddr2]='INS' then 'Inside'
when e.[empaddr2]='OUT' then 'Outside'
when e.[empaddr2]='CAB' then 'Cabinet'
When e.[empaddr2]='' then 'Undefined'
Else e.[empaddr2] End as Salesmantype.

Now Requirement based on s.[slmcode] 

please change the SalesmanType on the following individuals to "Outside"? Currently they are listed as "Undefined".

Mitchell Kahn (MKAHN)
Terri Zerby (TZERB)
Kevin Niblett (KNIBL)
Edwin Ramos (ERAMO)
Mike Noble (MNOBL)

How to achieve.

Plz explain in Detail with example or modify this code .

Thanks In Advance.

Gautam

[SharathPanackal]

Hi Gautam,

[Sales]:
LOAD

Branch, [Invoice Date], [Invoice#], [Salesman 1], 

if([Salesman 1]='Mitchell Kahn (MKAHN)','Outside',

  if([Salesman 1]='Terri Zerby (TZERB)','Outside',

     if([Salesman 1]='Kevin Niblett (KNIBL)','Outside',

        if([Salesman 1]='Edwin Ramos (ERAMO)','Outside',

           if([Salesman 1]='Mike Noble (MNOBL)','Outside' ))))) as Salesmantype
;

SQL
SELECT s.[whsnum] as [Branch]
,[invdate] as [Invoice Date]
,[invnum] as [Invoice #]
,s.[slmcode] as [Salesman 1]

,Case when e.[empaddr2]='INS' then 'Inside'
when e.[empaddr2]='OUT' then 'Outside'
when e.[empaddr2]='CAB' then 'Cabinet'
When e.[empaddr2]='' then 'Undefined'
Else e.[empaddr2] End as Salesmantype.

I think the if conditions can be written in a much better manner, but still this should work fine. I havent changed the SQL query but added code to the preceding LOAD statement.

[Gautam]

Hi Sharat,

Thanks for your Quick Reply, I will try the code and let you know.

Can I use Load * instead of individual column name in  ADDED code by you & If should work.

I have many Column names.

*************?????

LOAD

*;

if([Salesman 1]='Mitchell Kahn (MKAHN)','Outside',

  if([Salesman 1]='Terri Zerby (TZERB)','Outside',

     if([Salesman 1]='Kevin Niblett (KNIBL)','Outside',

        if([Salesman 1]='Edwin Ramos (ERAMO)','Outside',

           if([Salesman 1]='Mike Noble (MNOBL)','Outside' ))))) as Salesmantype

*******

[SharathPanackal]

Hi Gautam,

You can do this :

LOAD * ,

if([Salesman 1]='Mitchell Kahn (MKAHN)','Outside',

  if([Salesman 1]='Terri Zerby (TZERB)','Outside',

     if([Salesman 1]='Kevin Niblett (KNIBL)','Outside',

        if([Salesman 1]='Edwin Ramos (ERAMO)','Outside',

           if([Salesman 1]='Mike Noble (MNOBL)','Outside' ))))) as Salesmantype_New;

You can drop the original field after this load and use rename keyword later to change the name of the field ' Salesmantype_New' to 'Salesmantype'if you like. 

[Gautam]

Hi Sharat,
Thanks for the code and it worked 95%, When I use Rename Keyword to rename salesmantype_new to Salesmantype I am getting error as "Field already existed", when I use alias, shows as different dimension and data not Overwrite of Salesmantype with Salesmantype_new for those conditional SLMCODE.
Plz share your thoughts.
Thanks In Advance.

[SharathPanackal]

Hi Gautam,

I am sorry i missed adding the final else part inside the if condition

[Sales]:
LOAD *,

if([Salesman 1]='Mitchell Kahn (MKAHN)','Outside',

  if([Salesman 1]='Terri Zerby (TZERB)','Outside',

     if([Salesman 1]='Kevin Niblett (KNIBL)','Outside',

        if([Salesman 1]='Edwin Ramos (ERAMO)','Outside',

           if([Salesman 1]='Mike Noble (MNOBL)','Outside' ,Salesmantype_old))))) as Salesmantype
;

SQL
SELECT s.[whsnum] as [Branch]
,[invdate] as [Invoice Date]
,[invnum] as [Invoice #]
,s.[slmcode] as [Salesman 1]

,Case when e.[empaddr2]='INS' then 'Inside'
when e.[empaddr2]='OUT' then 'Outside'
when e.[empaddr2]='CAB' then 'Cabinet'
When e.[empaddr2]='' then 'Undefined'
Else e.[empaddr2] End as Salesmantype_old.

;

drop field Salesmantype_old;

Please click on Accept as solution if i was able to solve your issue. 

Regards,

Sharath Panackal

[Gautam]

Hi Sharat,
Salesmantype used across many sheets in the report and in Cycler, If I drop then I have to include Salesmantype_new field. I want to retain Salesmantype with over for those conditional SLMCODE and drop salesmantype_new.