topic Re: Read SQL data by data in QlikView

Read SQL data by data

Sat, 13 Apr 2013 06:38:04 GMT

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

Re: Read SQL data by data

Gysbert_Wassenaar — Sat, 13 Apr 2013 09:19:00 GMT

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:

Re: Read SQL data by data

Sun, 14 Apr 2013 07:51:47 GMT

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.

Re: Read SQL data by data

Gysbert_Wassenaar — Mon, 15 Apr 2013 08:08:34 GMT

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

Re: Read SQL data by data

Mon, 15 Apr 2013 09:07:31 GMT

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

Re: Read SQL data by data

Mon, 15 Apr 2013 11:31:55 GMT

Try this.

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

Re: Read SQL data by data

Gysbert_Wassenaar — Mon, 15 Apr 2013 12:17:21 GMT

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

Re: Read SQL data by data

Wed, 17 Apr 2013 12:51:30 GMT

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

Re: Read SQL data by data

Wed, 17 Apr 2013 13:30:42 GMT

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.

Re: Read SQL data by data

Sat, 20 Apr 2013 06:48:11 GMT

Finally it's working with your instructions, thanks!