Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Actually my scenario is like that:
i have one table like this:
Date , field1 , filed2 , field 3, count
02-12-2012 , A , B ,C , 100
02-12-2012 , X , Y ,Z , 200
03-12-2012 , A , B ,C , 300
03-12-2012 , M , N ,P , 400
04-12-2012 , A , B ,C , 500
04-12-2012 , X , Y ,Z , 200
02-12-2012 , M , N ,P , 600
Now i want to update this table by recursive sum base on key.
key = field1+field2+filed3.
If same key is coming further down for any date , it all add old count with new count and replace the new count. we can understand by this example:
Lets say in this above table, take 1st row so key ll be ABC and for this key count ll be 100.
Same key ABC is coming on date 03-12-2012 with new count 300. Now we ll add this new count to old count and replace it .
so now this row ll be
03-12-2012 , A , B ,C , 400(300+100)
again this same key is coming on date 04-12-2012,
after replacing this row ll be le
04-12-2012 , A , B ,C , 900(500+400)
Now there is no data for ABC key, so it ll go back and new key ll be XYZ.
we ll do same process for this key also.
In the same way we ll procees each key and update the table.
Final table ll be:
Date , field1 , filed2 , field 3, count
02-12-2012 , A , B ,C , 100
02-12-2012 , X , Y ,Z , 200
03-12-2012 , A , B ,C , 400(300+100)
03-12-2012 , M , N ,P , 400
04-12-2012 , A , B ,C , 900( 500+300+100)
04-12-2012 , X , Y ,Z , 400(200+200)
02-12-2012 , M , N ,P , 10000(600+400)
This should do the trick in script ...
Data:
LOAD RowNo() as rowIndx, * inline [
Date , field1 , filed2 , field 3, count
02-12-2012 , A , B ,C , 100
02-12-2012 , X , Y ,Z , 200
03-12-2012 , A , B ,C , 300
03-12-2012 , M , N ,P , 400
04-12-2012 , A , B ,C , 500
04-12-2012 , X , Y ,Z , 200
02-12-2012 , M , N ,P , 600];
left join (Data)
Load
field1 & filed2 & [field 3] as Key,
if(field1 & filed2 & [field 3]=peek(Key),count+peek(RunningCount),count) as RunningCount,
rowIndx
resident Data
order by field1, filed2, [field 3], rowIndx;
flipside
Hello,
Is it possible to do this in qlikView?
Nobody can help me ?
Regards
Rahul
It is not Clear???????
This should do the trick in script ...
Data:
LOAD RowNo() as rowIndx, * inline [
Date , field1 , filed2 , field 3, count
02-12-2012 , A , B ,C , 100
02-12-2012 , X , Y ,Z , 200
03-12-2012 , A , B ,C , 300
03-12-2012 , M , N ,P , 400
04-12-2012 , A , B ,C , 500
04-12-2012 , X , Y ,Z , 200
02-12-2012 , M , N ,P , 600];
left join (Data)
Load
field1 & filed2 & [field 3] as Key,
if(field1 & filed2 & [field 3]=peek(Key),count+peek(RunningCount),count) as RunningCount,
rowIndx
resident Data
order by field1, filed2, [field 3], rowIndx;
flipside
Thank you very much flipside.
This is exactly what i want.
Thanks alot.