How to change column values for few rows in qvd?

[#1]

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'

  )

    )

    or

    (DECLARATION_DATE = '18/07/2016' and

  SHOP_CODE = '1001GL0014' and

  (

  ITEM_NUMBER='SAM0116801' or

  ITEM_NUMBER='SAM0116802' or

  ITEM_NUMBER='SAM0116803'

  )

    )

    ,

    0,

    QTY_STOCK) as QTY_STOCK

FROM DATA.QVD

(qvd);

[avinashelite]

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'

  )

    )

    or

    (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'

  )

    )

    ,

    0,

    QTY_STOCK) as QTY_STOCK

FROM DATA.QVD

(qvd);

[jonathandienst]

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'

   )

  )

  or

  (DECLARATION_DATE = '18/07/2016' and

  SHOP_CODE = '1001GL0014' and

  (

   ITEM_NUMBER='SAM0116801' or

   ITEM_NUMBER='SAM0116802' or

   ITEM_NUMBER='SAM0116803'

  )

  ),

  0,

  QTY_STOCK

  ) as QTY_STOCK

FROM DATA.QVD

(qvd);

STORE Data into DATA.qvd (qvd);

[#4 · Accepted solution]

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

[#5]

Thanks also Jonathan for your help and proposition

[avinashelite]

Could you please mark the correct and helpful answers

[#7]

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?