Read SQL data by data

[#1]

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

[Gysbert_Wassenaar]

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:

[#3]

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.

[Gysbert_Wassenaar]

Please explain what you need. I don't understand your requirement.

[#5]

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! 😉

[#6]

Try this.

if(Activitity=previous(Activity), numsum(Progress,Peek(Accumulated))) as Accumulated

[Gysbert_Wassenaar · Accepted solution]

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

[#8]

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. 😉

[#9]

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.

[#10]

Finally it's working with your instructions, thanks!