topic Re: How to change column values for few rows in qvd? in QlikView

How to change column values for few rows in qvd?

Mon, 08 Aug 2016 11:58:40 GMT

Hi All,

I need to update the column QTY_STOCK by setting it to 0 based on certain conditions while reading it in my dashboard.

The qvd is being read when I'm using below script but the QTY_STOCK value is not being updated as per the below condition for date = '18/07/2016' and shop_code and item_number even though these records do exist in my qvd:

DECLARATION_DATE	SHOP_CODE	ITEM_NUMBER	QTY_STOCK
18/07/2016	1001BR0034	SAM0115201	2
18/07/2016	1001GL0014	SAM0115201	3
18/07/2016	1001BR0034	APP0168002	0
18/07/2016	1001GL0014	APP0168002	1
18/07/2016	1001BR0034	APP0168003	0
18/07/2016	1001GL0014	APP0168003	2
18/07/2016	1001GL0014	LGE0187001	1
18/07/2016	1001BR0034	SAM0115203	2
18/07/2016	1001GL0014	SAM0115203	3
18/07/2016	1001BR0034	LGE0185001	5
18/07/2016	1001GL0014	LGE0185001	3
18/07/2016	1001BR0034	HWI0119001	2
18/07/2016	1001GL0014	HWI0119001	2
18/07/2016	1001BR0034	SAM0115204	1
18/07/2016	1001BR0034	SAM0116803	1
18/07/2016	1001GL0014	SAM0116803	0
18/07/2016	1001BR0034	HWI0114004	0
18/07/2016	1001GL0014	HWI0114004	5
18/07/2016	1001BR0034	APP0168001	0
18/07/2016	1001GL0014	APP0168001	2
18/07/2016	1001BR0034	SAM0116801	3
18/07/2016	1001GL0014	SAM0116801	0
18/07/2016	1001BR0034	APP0169001	3
18/07/2016	1001GL0014	APP0169001	2
18/07/2016	1001BR0034	SAM0116802	4
18/07/2016	1001GL0014	SAM0116802	0
18/07/2016	1001BR0034	APP0168004	0
18/07/2016	1001GL0014	APP0168004	3

Could you please help?

Thanks in advance

LOAD

if((DECLARATION_DATE = '18/07/2016' and

SHOP_CODE = '1001BR0034' and

(

ITEM_NUMBER='APP0168001' or

ITEM_NUMBER='APP0168002' or

ITEM_NUMBER='APP0168003' or

ITEM_NUMBER='APP0168004'

)

(DECLARATION_DATE = '18/07/2016' and

SHOP_CODE = '1001GL0014' and

(

ITEM_NUMBER='SAM0116801' or

ITEM_NUMBER='SAM0116802' or

ITEM_NUMBER='SAM0116803'

)

QTY_STOCK) as QTY_STOCK

FROM DATA.QVD

(qvd);

Re: How to change column values for few rows in qvd?

avinashelite — Mon, 08 Aug 2016 12:20:52 GMT

You have not added all the combinations

Try like this

LOAD

if((DECLARATION_DATE = '18/07/2016' and

(SHOP_CODE = '1001BR0034' or SHOP_CODE ='1001GL0014')and

(

ITEM_NUMBER='APP0168001' or

ITEM_NUMBER='APP0168002' or

ITEM_NUMBER='APP0168003' or

ITEM_NUMBER='APP0168004'

)

(DECLARATION_DATE = '18/07/2016' and

(SHOP_CODE = '1001GL0014' or SHOP_CODE ='1001BR0034') and

(

ITEM_NUMBER='SAM0116801' or

ITEM_NUMBER='SAM0116802' or

ITEM_NUMBER='SAM0116803'

)

QTY_STOCK) as QTY_STOCK

FROM DATA.QVD

(qvd);

Re: How to change column values for few rows in qvd?

jonathandienst — Mon, 08 Aug 2016 12:24:36 GMT

You have most of it already:

Data:

LOAD

DECLARATION_DATE,

SHOP_CODE,

ITEM_NUMBER,

If(

(DECLARATION_DATE = '18/07/2016' and

SHOP_CODE = '1001BR0034' and

(

ITEM_NUMBER='APP0168001' or

ITEM_NUMBER='APP0168002' or

ITEM_NUMBER='APP0168003' or

ITEM_NUMBER='APP0168004'

)

(DECLARATION_DATE = '18/07/2016' and

SHOP_CODE = '1001GL0014' and

(

ITEM_NUMBER='SAM0116801' or

ITEM_NUMBER='SAM0116802' or

ITEM_NUMBER='SAM0116803'

)

QTY_STOCK

) as QTY_STOCK

FROM DATA.QVD

(qvd);

STORE Data into DATA.qvd (qvd);

Re: How to change column values for few rows in qvd?

Mon, 08 Aug 2016 12:33:57 GMT

woah that's cool!!! THanks Avinash R, your code is working great!

Re: How to change column values for few rows in qvd?

Mon, 08 Aug 2016 12:34:23 GMT

Thanks also Jonathan for your help and proposition

Re: How to change column values for few rows in qvd?

avinashelite — Mon, 08 Aug 2016 12:35:45 GMT

Could you please mark the correct and helpful answers

Re: How to change column values for few rows in qvd?

Mon, 08 Aug 2016 12:39:16 GMT

Done

Could you please also help in a topic related with this one?

How to count num shops having stock 0 grouped at shop level?