topic Load Data with GROUP BY in QlikView

Load Data with GROUP BY

Mon, 07 Sep 2015 10:35:23 GMT

hi i have a question about loading data in qlikview script

i have two fact table called header and line.

i want to summarize quantity in line table by using this script:

load

.........

........

(sum(QTY1)/sum(QTY2)) as VALUE_PERCEN

..........

Resident a

Group by

.........

...........

........

;

All value work fine, but why the value of VALUE_PERCEN return 0 (zero).

Anybody knows why ?

Really need your help.

thanks

Re: Load Data with GROUP BY

alexandros17 — Mon, 07 Sep 2015 10:38:07 GMT

The syntax seems correct, please give us an example with data

Re: Load Data with GROUP BY

Mon, 07 Sep 2015 10:38:46 GMT

Hi,

can you sent the detailed script, then it might be easier.

Re: Load Data with GROUP BY

qlikviewwizard — Mon, 07 Sep 2015 10:53:28 GMT

Hi,

If sum(QTY1)=0 then Percentage will be zero.

0/some value other than zero = zero

0/0=undefined

some value/0=undifined.

Hope this will Helpful to you. Thank you.

EMP:

LOAD EMPNO,

ENAME,

JOB,

MGR,

DATE(HIREDATE) AS HIREDATE,

YEAR( DATE(HIREDATE)) AS YAER,

SAL as Salary,

COMM,

DEPTNO

FROM

EMP.xlsx

(ooxml, embedded labels, table is EMP);

Emp_Data:

Load

YEAR,

JOB,

DEPTNO,

if(SUM(COMM)=0,0,sum(SAL)/SUM(COMM)) as VALUE_PERCEN

RESIDENT EMP

group by YEAR,JOB,DEPTNO;

Re: Load Data with GROUP BY

Peter_Cammaert — Mon, 07 Sep 2015 10:56:58 GMT

This code :

RawData:

LOAD * INLINE [

RID, Item, Location, QTY1, QTY2

1, AAA, 1, 100, 120

2, BBB, 1, 200, 210

3, CCC, 1, 1000, 1010

4, AAA, 2, 100, 120

5, BBB, 2, 199, 208

6, CCC, 2, 1100, 1100

7, AAA, 3, 100, 120

8, BBB, 3, 150, 200

9, CCC, 3, 900, 1000

];

Process:

LOAD Item,

Sum(QTY1) AS TotalQTY1,

Sum(QTY2) AS TotalQTY2,

(Sum(QTY1)/Sum(QTY2)) AS VALUE_PERCEN

RESIDENT RawData

GROUP BY Item;

produces this result:

Probably something is wrong in the script parts that you are not showing...

Peter

Re: Load Data with GROUP BY

Tue, 08 Sep 2015 03:11:34 GMT

Hi All,

Really sorry for late reply

Yes I already know the problem. It is because the data not seperated between Quantity1 and Quantity2. There is only one field called Quantity. So the difference is on trx_id column, each header_id has one trx_id, if trx_id=1, so the quantity is Qty1 and if trx_id=2, so the quantity is Qty2.

So when i grouped the data, when Qty1 appeared, Qty2 isn't appeared.

I think it cant be grouped/summarize. Or anybody has another idea ?

Unfortunately i cant share the detail data.

Thanks

Re: Load Data with GROUP BY

sunny_talwar — Tue, 08 Sep 2015 03:31:14 GMT

Try this may be:

Temp

LOAD ....

....

Quantity as QTY1

....

Resident a

Where trx_id = 1;

Join (Temp)

LOAD ....

....

Quantity as QTY2

....

Resident a

Where trx_id = 2;

LOAD

.........

........

(sum(QTY1)/sum(QTY2)) as VALUE_PERCEN

..........

Resident Temp

Group by

.........

...........

........

Re: Load Data with GROUP BY

Tue, 08 Sep 2015 03:37:32 GMT

Hi Sunny,

thanks for your idea.

But i think it won't work Sunny, bcause I have one field name called HeaderID that link the table to the Header Table.

Each Header ID only has one trx_id. So if QTY1 (trx_id=1) has a value, QTY2 (trx_id=2) will not has a value.

Like this

HeaderID QTY1 QTY2 ValuePercen

1 20 0 0/20 = 0

2 0 10 10/0 = -

Sorry for the unclear explanation.

Thanks

Re: Load Data with GROUP BY

sunny_talwar — Tue, 08 Sep 2015 03:45:35 GMT

If you are grouping by Header ID then yes you are right, in which case I would ask you why are you even Sum(QTY1) when there is only 1 value. Also what is the point of calculating this percentage when one of the values will always be 0?

I guess we need to know what is your end goal here.

Re: Load Data with GROUP BY

Tue, 08 Sep 2015 03:56:29 GMT

Hi Sunny,

First one, i'm using grouped by header_id bcause i need to join with another table called TABLE_HEADER, so i need to show header_id in DETAIL_TABLE.

Second one, Actually there are not only 1 value, there are many header_id and each header_id has one value.

Yap, before i'm using group by, i'm using set analysis to calculate the percentage

like this

sum({<trx_id={2}>}Quantity)/sum({<trx_id={1}>}Quantity). So actually there is only one field called QUANTITY and divided into (distinguished by) two trx_id.

Thanks

Re: Load Data with GROUP BY

sunny_talwar — Tue, 08 Sep 2015 04:18:49 GMT

Why don't you join first (so that you don't have to group by Header_ID) and then calculate the percentage, because the current method won't give you meaningful results.

Re: Load Data with GROUP BY

Tue, 08 Sep 2015 04:26:43 GMT

Hi Sunny,

Do you mean left join between header and detail table ? so it will be only one table ?

Can you give me the example Sunny ? still dont get the idea

thanks

Re: Load Data with GROUP BY

sunny_talwar — Tue, 08 Sep 2015 05:32:42 GMT

Would you be able to share your current script (ideally the complete script)? We here might be able to suggest you something.