Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I want to read an SQL table data by data. I want this because I want to have the name of the columns as a data, remove all zeros and get some extra information. An example:
In the database the source table is like (but with a lot of columns more):
Activity | 10/04/2013 | 11/04/2013 | 12/04/2013 |
---|---|---|---|
Act1 | 1.2 | 2 | 0 |
Act2 | 0 | 3 | 2 |
Act3 | 0 | 0 | 1 |
I want to read that table and transform in one like this:
Activity | Date | Progress | Accummulated |
---|---|---|---|
Act1 | 10/04/2013 | 1.2 | 1.2 |
Act1 | 11/04/2013 | 2 | 3.2 |
Act2 | 11/04/2013 | 3 | 3 |
Act2 | 12/04/2013 | 2 | 5 |
Act3 | 12/04/2013 | 1 | 1 |
I've done this in a macro excel but I don't know how to do this in QlikView, any suggestion?
Thanks in advance.
Jose
Ah. I see my mistake now. Try this:
Temp:
CrossTable(Date,Progress,1)
LOAD * INLINE [
Activity, 10/04/2013, 11/04/2013, 12/04/2013, 13/04/2013, 14/04/2013
Act1, 1.2, 2, 0, 0, 0
Act2, 0, 3, 2, 0, 0
Act3, 0, 0, 1, 0, 0
Act4, 0, 3, 2, 2, 4
];
Temp2:
Load Activity, Date, Progress,
if(Activity=previous(Activity),rangesum(Progress,peek(Accummulated)),Progress) as Accummulated
Resident Temp
order by Activity, Date;
Result:
NoConcatenate
load * Resident Temp2
where Progress <> 0;
Drop table Temp, Temp2
You can use the crosstable function for this. Something like this:
Temp:
crosstable(Date, Progress,1)
load * from ...somewhere...;
Result:
Load Activity, Date, Progress,
if(Activitity=previous(Activity),rangesum(Progress,previous(Progress)),Progress) as Accummulated
Resident Temp
where Progress <> 0
order by Activity, Date;
Drop table Temp:
Thanks a lot Gysbert!!! The dates are right. I didn't know the crosstable function. 😉
I have a problem with the accummulate quantity. Using your code, the sum is for the last 2 progress. I need a way to sum the progress in that row plus accummulate till last one.
Please explain what you need. I don't understand your requirement.
I'm sorry, I'll try to explain it better.
With the example I put yesterday, it works, but because there are 2 entries for each activity, with 3 or more it doesn't work because the accumulated quantity is more than the progress of one row and just one previous.
Activity | Date | Progress | Accumulated |
---|---|---|---|
Act4 | 11/04/2013 | 3 | 3 |
Act4 | 12/04/2013 | 2 | 5 |
Act4 | 13/04/2013 | 2 | 7 |
Act4 | 14/04/2013 | 4 | 11 |
As you can see in the previous table, the accumulated quantity in the latest row it's 11, and with your code it's 6 (the sum of the progress of that row and previous one, not all of previous).
is there any way to get that accumulated value?
Thanks a lot! 😉
Try this.
if(Activitity=previous(Activity), numsum(Progress,Peek(Accumulated))) as Accumulated
Ah. I see my mistake now. Try this:
Temp:
CrossTable(Date,Progress,1)
LOAD * INLINE [
Activity, 10/04/2013, 11/04/2013, 12/04/2013, 13/04/2013, 14/04/2013
Act1, 1.2, 2, 0, 0, 0
Act2, 0, 3, 2, 0, 0
Act3, 0, 0, 1, 0, 0
Act4, 0, 3, 2, 2, 4
];
Temp2:
Load Activity, Date, Progress,
if(Activity=previous(Activity),rangesum(Progress,peek(Accummulated)),Progress) as Accummulated
Resident Temp
order by Activity, Date;
Result:
NoConcatenate
load * Resident Temp2
where Progress <> 0;
Drop table Temp, Temp2
Thanks guys.
I don't know why Previous() is not giving me the previous data, it always returns the same value than Activity. It's seems like previous is doing nothing.
Then, Activity = previous(Activity) and Accumulated is the sum of the hole column.
Thanks agains. 😉
Well, I've discovered the problem for previous() function. Once I solve it, the problem is other.
The function "peek(Accum)" returns a complete sum of the whole column till that row, despite of one of them the value was just "progress"(when the condition Activity=previous(Activity) is false). In the next row it returns the whole sum again.
Thanks.
Finally it's working with your instructions, thanks!