Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)'
;
NEXT
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
Hi
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;
Hi
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;
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)'
;
NEXT
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)'
;
NEXT
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)'
;
NEXT
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)'
;
NEXT
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