Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have raw table1 as below. ITEM_No is product identity; Qty is the need for each STO; Stocks is the same for each product.
Below Table2 is the result i want to get. For each distinct article, when the Stocks_Remaining below zero, i wish to have a comment as "N" and before the Stocks_Remaining reach zero, i wish to have a comment as "Y" for these stores.
Attached are an QVW file incudling the raw data and an excel file that shows how we do it in excel.
I have no idea how to get this result table through Qlikview. Could someone help here in QLIKVIEW?
Table1 | |||
ITEM_NO | STO Number | QTY | Stocks |
00169560 | 001 | 8 | 200 |
00169560 | 002 | 8 | 200 |
00169560 | 003 | 8 | 200 |
00169560 | 004 | 8 | 200 |
00169560 | 005 | 8 | 200 |
00169560 | 006 | 8 | 200 |
00169560 | 007 | 16 | 200 |
00169560 | 008 | 8 | 200 |
00169560 | 009 | 8 | 200 |
00169560 | 010 | 8 | 200 |
00169560 | 011 | 8 | 200 |
00169560 | 012 | 8 | 200 |
00169560 | 013 | 8 | 200 |
00169560 | 014 | 8 | 200 |
00169560 | 015 | 16 | 200 |
00169560 | 016 | 16 | 200 |
00169560 | 017 | 8 | 200 |
00169560 | 018 | 8 | 200 |
00169560 | 019 | 8 | 200 |
00169560 | 020 | 8 | 200 |
00169560 | 021 | 16 | 200 |
00169560 | 022 | 16 | 200 |
00169560 | 023 | 8 | 200 |
00169560 | 024 | 8 | 200 |
00169560 | 025 | 8 | 200 |
00169560 | 026 | 8 | 200 |
00169560 | 027 | 16 | 200 |
00169560 | 028 | 24 | 200 |
30234229 | 001 | 10 | 20 |
30234229 | 002 | 10 | 20 |
30234229 | 003 | 10 | 20 |
30234229 | 004 | 10 | 20 |
30234229 | 005 | 10 | 20 |
30234229 | 006 | 10 | 20 |
30234229 | 007 | 10 | 20 |
30234229 | 008 | 10 | 20 |
30234229 | 009 | 10 | 20 |
30234229 | 010 | 10 | 20 |
Table2 | |||||
ITEM_NO | STO Number | QTY | Stocks | StocksRemaining_Calculation | Decision |
00169560 | 001 | 8 | 200 | 192 | Y |
00169560 | 002 | 8 | 200 | 184 | Y |
00169560 | 003 | 8 | 200 | 176 | Y |
00169560 | 004 | 8 | 200 | 168 | Y |
00169560 | 005 | 8 | 200 | 160 | Y |
00169560 | 006 | 8 | 200 | 152 | Y |
00169560 | 007 | 16 | 200 | 136 | Y |
00169560 | 008 | 8 | 200 | 128 | Y |
00169560 | 009 | 8 | 200 | 120 | Y |
00169560 | 010 | 8 | 200 | 112 | Y |
00169560 | 011 | 8 | 200 | 104 | Y |
00169560 | 012 | 8 | 200 | 96 | Y |
00169560 | 013 | 8 | 200 | 88 | Y |
00169560 | 014 | 8 | 200 | 80 | Y |
00169560 | 015 | 16 | 200 | 64 | Y |
00169560 | 016 | 16 | 200 | 48 | Y |
00169560 | 017 | 8 | 200 | 40 | Y |
00169560 | 018 | 8 | 200 | 32 | Y |
00169560 | 019 | 8 | 200 | 24 | Y |
00169560 | 020 | 8 | 200 | 16 | Y |
00169560 | 021 | 16 | 200 | 0 | Y |
00169560 | 022 | 16 | 200 | -16 | N |
00169560 | 023 | 8 | 200 | -24 | N |
00169560 | 024 | 8 | 200 | -32 | N |
00169560 | 025 | 8 | 200 | -40 | N |
00169560 | 026 | 8 | 200 | -48 | N |
00169560 | 027 | 16 | 200 | -64 | N |
00169560 | 028 | 24 | 200 | -88 | N |
30234229 | 001 | 10 | 20 | 10 | Y |
30234229 | 002 | 10 | 20 | 0 | Y |
30234229 | 003 | 10 | 20 | -10 | N |
30234229 | 004 | 10 | 20 | -20 | N |
30234229 | 005 | 10 | 20 | -30 | N |
30234229 | 006 | 10 | 20 | -40 | N |
30234229 | 007 | 10 | 20 | -50 | N |
30234229 | 008 | 10 | 20 | -60 | N |
30234229 | 009 | 10 | 20 | -70 | N |
30234229 | 010 | 10 | 20 | -80 | N |
Hi,
Please find in attached the Qlikview file
output:
Hi,
Please find in attached the Qlikview file
output:
Hi Taoufig,
That works and help me to expand my knowledge.
Really thanks for your professional answer.