topic Re: Update row value in App Development

Update row value

Gautam — Tue, 04 Dec 2018 17:27:38 GMT

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.

Re: Update row value

SharathPanackal — Tue, 04 Dec 2018 18:25:19 GMT

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
];

Re: Update row value

SharathPanackal — Tue, 04 Dec 2018 18:39:19 GMT

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

Re: Update row value

Gautam — Tue, 04 Dec 2018 18:46:40 GMT

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

Re: Update row value

SharathPanackal — Tue, 04 Dec 2018 19:03:13 GMT

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]

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.

Re: Update row value

Gautam — Tue, 04 Dec 2018 19:42:42 GMT

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

*******

Re: Update row value

SharathPanackal — Tue, 04 Dec 2018 21:16:15 GMT

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.

Re: Update row value

Gautam — Wed, 05 Dec 2018 13:54:21 GMT

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.

Re: Update row value

SharathPanackal — Wed, 05 Dec 2018 14:07:23 GMT

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]

;

drop field Salesmantype_old;

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

Regards,

Sharath Panackal

Re: Update row value

Gautam — Wed, 05 Dec 2018 14:22:01 GMT

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.

Re: Update row value

SharathPanackal — Wed, 05 Dec 2018 14:33:07 GMT

Hi Gautam,

In my solution i have aliased the field e.[empaddr2] to Salesmantype_old. I have then performed extra transformation to the field Salesmantype_old inside the preceding LOAD and renamed it back to Salesmantype.

The last drop statement was just to remove the field Salesmantype_old. You will still have field Salesmantype at the end.

If you are confused, you can even remove the drop statement for field Salesmantype_old and it should still work.

Regards,

Sharath Panackal