
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Tags:
- new_to_qlikview
- « Previous Replies
-
- 1
- 2
- Next Replies »


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The syntax seems correct, please give us an example with data

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
can you sent the detailed script, then it might be easier.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
.........
...........
........

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- « Previous Replies
-
- 1
- 2
- Next Replies »