Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table as follows:
Particulars | Qty | Unit Rate | Amount |
item A | 1 | 25 | 0 |
production | 1 | 25 | 0 |
item B | 20 | 25 | 0 |
production | 1 | 25 | 0 |
item C | 5 | 25 | 125 |
item D | 10 | 25 | 250 |
item E | 10 | 25 | 250 |
item F | 10 | 25 | 0 |
production | 1 | 25 | 0 |
item G | 20 | 25 | 500 |
The logic is as follows:
if the word production appear the row that row and the row above should not be calculated. can anyone tell how to solve this using peek or previous function.
will be great if i get results in Qlikview
Maybe this is helpful: http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/08/peek-vs-previous-when-to-use-each
- Marcus
See attached qvw.
try with this code
AAA:
load * inline [
Particulars, Qty, Unit, Rate, Amount
item, A, 1, 25, 0
production, ,1, 25, 0
item, B, 20, 25, 0
production,, 1, 25, 0
item, C, 5, 25, 125
item, D, 10, 25, 250
item, E, 10, 25, 250
item, F, 10, 25, 0
production,, 1, 25, 0
item, G, 20, 25, 500
];
BBB:
NoConcatenate
LOAD RowNo() as nriga, *, If(Particulars='production',1,0) as flag Resident AAA;
DROP Table AAA;
CCC: NoConcatenate
LOAD nriga as toDel Resident BBB Where flag=1;
Concatenate
LOAD Previous(nriga) as toDel Resident BBB Where flag=1;
TEMP: NoConcatenate
LOAD * Resident BBB;
Left Join
LOAD toDel as nriga, 1 as NewFlag Resident CCC;
DROP Tables BBB, CCC;
Final: NoConcatenate
LOAD * Resident TEMP Where NewFlag <> 1;
DROP Table TEMP;
table1:
LOAD Particulars,
Qty,
[Unit Rate],
RecNo() as LoadOrder
FROM [http://community.qlik.com/thread/149240] (html, codepage is 1252, embedded labels, table is @1);
Left Join (table1)
LOAD LoadOrder,
If(Match('production',Particulars,Previous(Particulars)),0,Qty*[Unit Rate]) as Amount
Resident table1
Order By LoadOrder desc;
hope this helps
regards
Marco