Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load Data with GROUP BY

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

12 Replies
alexandros17
Partner - Champion III

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

Not applicable
Author

Hi,

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

qlikviewwizard
Master II

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;

Capture.JPG

Peter_Cammaert
Partner - Champion III

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:

GROUPBYSUM Example thread179476.jpg

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

Peter

Not applicable
Author

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

.........

...........

........

Not applicable
Author

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.

Not applicable
Author

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