topic Re: How to avoid duplicate rows after doing calculations in QlikView

How to avoid duplicate rows after doing calculations

Sarath123 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the data for which sales has to be calculated based on code.I need to calculate both company and total sales.
For each code I have sales for 9 different quarters.
My requirement will be
for code(1201) sales shuld be sum of sales for all 9 quarters
for code(1530) sales should be sum(sales(code(1530)-code(1547)-code(15500140)-code(15500140)-code(15904001)-code(15904002)-code(15904003)

I defined my requirement on the script.

NoConcatenate
TABLE:
LOAD
Code,
Sum(TU_Company_QTR) AS TU_Company_QTR,
Sum(TU_Total_QTR) AS TU_Total_QTR

FROM
[H:\New.Austria.data.xlsx]
(ooxml, embedded labels, table is Document_TB05)
Group by Code
;

FOR n = 0 to NoOfRows ('TABLE')

LET Code= Peek('Code',$(n),'TABLE');

Left Join (TABLE)
LOAD
TU_Company_QTR AS TU_Company_QTR_$(Code),
TU_Total_QTR AS TU_Total_QTR_$(Code)
Resident TABLE
Where Code= '$(Code)'
;

NoConcatenate
TOTAL:
LOAD
Code,
If(Code=1530, TU_Company_QTR-TU_Company_QTR_1547-TU_Company_QTR_15500140-TU_Company_QTR_15500240-TU_Company_QTR_15904001-TU_Company_QTR_15904002-TU_Company_QTR_15904003,
If(Code=1590, TU_Company_QTR-TU_Company_QTR_15904001+TU_Company_QTR_50904002+TU_Company_QTR_50904003,
TU_Company_QTR)) AS TU_Company_QTR_New,
TU_Company_QTR AS TU_Company_QTR_OLD,
If(Code=1530, TU_Total_QTR-TU_Total_QTR_1547-TU_Total_QTR_15500140-TU_Total_QTR_15500240-TU_Total_QTR_15904001-TU_Total_QTR_15904002-TU_Total_QTR_15904003,
If(Code=1590, TU_Total_QTR-TU_Total_QTR_15904001+TU_Total_QTR_50904002+TU_Total_QTR_50904003,
TU_Total_QTR)) AS TU_Total_QTR_New,
TU_Total_QTR AS TU_Total_QTR_OLD
Resident TABLE;
DROP Table TABLE;

I got the table with the sum of all values along with my calculated rows. I got a problem here.
I am getting 2 rows which is sum of sales for 1530 by adding 9 quarters and one rows with my calculation sales
code(1530) is sum(sales(code(1530)-code(1547)-code(15500140)-code(15500140)-code(15904001)-code(15904002)-code(15904003), But I need only my calculated sales rows

code Company sales Total sales

1530 190000 16667000

1530 5000 -2072000

Can someone help me to find a solution for this

Re: How to avoid duplicate rows after doing calculations

StarinieriG — Tue, 17 Sep 2019 13:02:35 GMT

try to add a group by

NoConcatenate
TOTAL:
LOAD
Code,
Sum(If(Code=1530, TU_Company_QTR-TU_Company_QTR_1547-TU_Company_QTR_15500140-TU_Company_QTR_15500240-TU_Company_QTR_15904001-TU_Company_QTR_15904002-TU_Company_QTR_15904003,
If(Code=1590, TU_Company_QTR-TU_Company_QTR_15904001+TU_Company_QTR_50904002+TU_Company_QTR_50904003,
TU_Company_QTR))) AS TU_Company_QTR_New,
Sum(TU_Company_QTR) AS TU_Company_QTR_OLD,
Sum(If(Code=1530, TU_Total_QTR-TU_Total_QTR_1547-TU_Total_QTR_15500140-TU_Total_QTR_15500240-TU_Total_QTR_15904001-TU_Total_QTR_15904002-TU_Total_QTR_15904003,
If(Code=1590, TU_Total_QTR-TU_Total_QTR_15904001+TU_Total_QTR_50904002+TU_Total_QTR_50904003,
TU_Total_QTR))) AS TU_Total_QTR_New,
Sum(TU_Total_QTR) AS TU_Total_QTR_OLD
Resident TABLE

Group by Code

;
DROP Table TABLE;

Re: How to avoid duplicate rows after doing calculations

Sarath123 — Tue, 17 Sep 2019 13:45:45 GMT

I tried with above script but I am still getting the same error

Code TU_Company_QTR_New TU_Total_QTR_New
1530 5000 -2072000
1530 190000 16667000

My script is below

NoConcatenate
TABLE:
LOAD
Code,
Sum(TU_Company_QTR) AS TU_Company_QTR,
Sum(TU_Total_QTR) AS TU_Total_QTR

FROM
[H:\New.Austria.data.xlsx]
(ooxml, embedded labels, table is Document_TB05)
Group by Code
;

FOR n = 0 to NoOfRows ('TABLE')

LET Code= Peek('Code',$(n),'TABLE');

Left Join (TABLE)
LOAD
TU_Company_QTR AS TU_Company_QTR_$(Code),
TU_Total_QTR AS TU_Total_QTR_$(Code)
Resident TABLE
Where Code= '$(Code)'
;

NoConcatenate
TOTAL:
LOAD
Code,
sum(If(Code=1530, TU_Company_QTR-TU_Company_QTR_1547-TU_Company_QTR_15500140-TU_Company_QTR_15500240-TU_Company_QTR_15904001-TU_Company_QTR_15904002-TU_Company_QTR_15904003,
If(Code=1590, TU_Company_QTR-TU_Company_QTR_15904001+TU_Company_QTR_15904002+TU_Company_QTR_15904003,
TU_Company_QTR))) AS TU_Company_QTR_New,
sum(TU_Company_QTR) AS TU_Company_QTR_OLD,
sum(If(Code=1530, TU_Total_QTR-TU_Total_QTR_1547-TU_Total_QTR_15500140-TU_Total_QTR_15500240-TU_Total_QTR_15904001-TU_Total_QTR_15904002-TU_Total_QTR_15904003,
If(Code=1590, TU_Total_QTR-TU_Total_QTR_15904001+TU_Total_QTR_15904002+TU_Total_QTR_15904003,
TU_Total_QTR))) AS TU_Total_QTR_New,
sum(TU_Total_QTR) AS TU_Total_QTR_OLD
Resident TABLE
Group by Code;
DROP Table TABLE;

NoConcatenate
TABLE1:
LOAD Code,
Sum(TU_Company_QTR) AS TU_Company_QTR,
Sum(TU_Total_QTR) AS TU_Total_QTR
FROM
[H:\New.Austria.data.xlsx]
(ooxml, embedded labels, table is Document_TB05)
Group By Code
;

FOR n = 0 to NoOfRows ('TABLE1')

LET Code= Peek('Code',$(n),'TABLE1');

Left Join (TABLE1)
LOAD
TU_Company_QTR AS TU_Company_QTR_$(Code),
TU_Total_QTR AS TU_Total_QTR_$(Code)
Resident TABLE1
Where Code= '$(Code)'
;

NEXT
NoConcatenate
TOTAL:
LOAD
Code,
sum(If(Code=1550, TU_Company_QTR-TU_Company_QTR_15500140+TU_Company_QTR_15500240,
If(Code=1547, TU_Company_QTR-TU_Company_QTR_15500140-TU_Company_QTR_15500240-TU_Company_QTR_15904001-TU_Company_QTR_15904002-TU_Company_QTR_15904003,
TU_Company_QTR))) AS TU_Company_QTR_New,
sum(TU_Company_QTR) AS TU_Company_QTR_OLD,
sum(If(Code=1550, TU_Total_QTR-TU_Total_QTR_15500140+TU_Total_QTR_15500240,
If(Code=1547, TU_Total_QTR-TU_Total_QTR_15500140-TU_Total_QTR_15500240-TU_Total_QTR_15904001-TU_Total_QTR_15904002-TU_Total_QTR_15904003,
TU_Total_QTR))) AS TU_Total_QTR_New,
sum(TU_Total_QTR) AS TU_Total_QTR_OLD
Resident TABLE1
Group by Code;

DROP Table TABLE1;

NoConcatenate
TABLE2:
LOAD Code,
Sum(TU_Company_QTR) AS TU_Company_QTR,
Sum(TU_Total_QTR) AS TU_Total_QTR
FROM
[H:\New.Austria.data.xlsx]
(ooxml, embedded labels, table is Document_TB05)
Group By Code
;

FOR n = 0 to NoOfRows ('TABLE2')

LET Code= Peek('Code',$(n),'TABLE2');

Left Join (TABLE2)
LOAD
TU_Company_QTR AS TU_Company_QTR_$(Code),
TU_Total_QTR AS TU_Total_QTR_$(Code)
Resident TABLE2
Where Code= '$(Code)'
;

NoConcatenate
TOTAL:
LOAD
Code,
sum(If(Code=1170, TU_Company_QTR-TU_Company_QTR_11700101,
If(Code=15500140, TU_Company_QTR+TU_Company_QTR_15500240,
TU_Company_QTR))) AS TU_Company_QTR_New,
sum(TU_Company_QTR) AS TU_Company_QTR_OLD,
sum(If(Code=1170, TU_Total_QTR-TU_Total_QTR_11700101,
If(Code=15500140, TU_Total_QTR+TU_Total_QTR_15500240,
TU_Total_QTR))) AS TU_Total_QTR_New,
sum(TU_Total_QTR) AS TU_Total_QTR_OLD
Resident TABLE2
Group by Code;

DROP Table TABLE2;

NoConcatenate
TABLE3:
LOAD Code,
Sum(TU_Company_QTR) AS TU_Company_QTR,
Sum(TU_Total_QTR) AS TU_Total_QTR
FROM
[H:\New.Austria.data.xlsx]
(ooxml, embedded labels, table is Document_TB05)
Group By Code
;

FOR n = 0 to NoOfRows ('TABLE3')

LET Code= Peek('Code',$(n),'TABLE3');

Left Join (TABLE3)
LOAD
TU_Company_QTR AS TU_Company_QTR_$(Code),
TU_Total_QTR AS TU_Total_QTR_$(Code)
Resident TABLE3
Where Code= '$(Code)'
;

NoConcatenate
TOTAL:
LOAD
Code,
sum(If(Code=15904001, TU_Company_QTR+TU_Company_QTR_15904002+TU_Company_QTR_15904003,
If(Code=3101, TU_Company_QTR-TU_Company_QTR_31010140-TU_Company_QTR_31010160-TU_Company_QTR_31010107,
TU_Company_QTR))) AS TU_Company_QTR_New,
sum(TU_Company_QTR) AS TU_Company_QTR_OLD,
sum(If(Code=15904001, TU_Total_QTR+TU_Total_QTR_15904002+TU_Total_QTR_15904003,
If(Code=3101, TU_Total_QTR-TU_Total_QTR_31010140-TU_Total_QTR_31010160-TU_Total_QTR_31010107,
TU_Total_QTR))) AS TU_Total_QTR_New,
sum(TU_Total_QTR) AS TU_Total_QTR_OLD
Resident TABLE3
Group by Code;

DROP Table TABLE3;

NoConcatenate
TABLE4:
LOAD Code,
Sum(TU_Company_QTR) AS TU_Company_QTR,
Sum(TU_Total_QTR) AS TU_Total_QTR
FROM
[H:\New.Austria.data.xlsx]
(ooxml, embedded labels, table is Document_TB05)
Group by Code
;

FOR n = 0 to NoOfRows ('TABLE4')

LET Code= Peek('Code',$(n),'TABLE4');

Left Join (TABLE4)
LOAD
3201 AS Code,
TU_Company_QTR AS TU_Company_QTR_$(Code),
TU_Total_QTR AS TU_Total_QTR_$(Code)
Resident TABLE4
Where Code= '$(Code)'
;

NoConcatenate
TOTAL:
LOAD
Code,
sum(If(Code= '3201', TU_Company_QTR-TU_Company_QTR_32010140-TU_Company_QTR_32010160-TU_Company_QTR_32010107,TU_Company_QTR)) AS TU_Company_QTR_New,
sum(TU_Company_QTR) AS TU_Company_QTR_OLD,
sum(If(Code= '3201', TU_Total_QTR-TU_Total_QTR_32010140-TU_Total_QTR_32010160-TU_Total_QTR_32010107,TU_Total_QTR)) AS TU_Total_QTR_New,
sum(TU_Total_QTR) AS TU_Total_QTR_OLD
Resident TABLE4
Group by Code;

DROP Table TABLE4;

Can you please have a look on my script and let me know if there is any error which has to be corrected