Load Data with GROUP BY

[#1]

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

[alexandros17]

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

[#3]

Hi,

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

[qlikviewwizard]

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;

[Peter_Cammaert]

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

[#6]

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

[sunny_talwar]

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

.........

...........

........

[#8]

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

[sunny_talwar]

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.

[#10]

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