calculation on the load script

[rajkumarb]

Hi

I got the below data, I have to load only the ORDERNUBER where count(ORDERNUBER )>1 and

if count(ORDERNUBER )>1 then sum(AMOUNT) > 0

I have used the below load

Book_TEMP:
LOAD DocumentNumber,
     ORDERNUBER,
     TransactionType,
     AMOUNT
FROM [Book12.xlsx]

(ooxml, embedded labels, table is Sheet1);

left join(Book_TEMP)
load distinct ORDERNUBER,
if(count(ORDERNUBER)>1,1,0)  as Flag
Resident Book_TEMP
group by ORDERNUBER;

data is

DocumentNumber	ORDERNUBER	TransactionType	AMOUNT
101	2A101	P1	10
102	2A102	P1	20
103	2A103	P1	30
104	2A104	P1	40
105	2A105	P1	50
106	2A106	P1	60
107	2A107	P1	70
108	2A108	P1	80
109	2A109	P1	90
110	2A110	P1	100
111	2A111	P1	110
112	2A112	P1	120
113	2A101	P2	-10
114	2A102	P2	-20
115	2A103	P2	-30
116	2A104	P2	-40
117	2A105	P2	-30
118	2A106	P2	-20

[sunny_talwar]

What is the output you are hoping to get?

[rajkumarb]

Hi Sunny I am hoping to get an output of 1)list of ORDERNUBER which occurred twice or more and 2) their sum(AMOUNT) value should be greater that '0' I have achieved the list of 1) in the above script but trying to include the 2) in the same script

[lironbaram · Accepted solution]

hi 

you can add sum amount 

to your expression 

left join(Book_TEMP)
load distinct ORDERNUBER,
if(count(ORDERNUBER)>1 and Sum(AMOUNT)>0,1,0)  as Flag
Resident Book_TEMP
group by ORDERNUBER;

[jonathandienst]

This code will not work:

left join(Book_TEMP)
load distinct ORDERNUBER,
	if(count(ORDERNUBER)>1,1,0)  as Flag
Resident Book_TEMP
group by ORDERNUBER;

When you group by ORDERNUBER, the count will always be 1. You need to use a different field for the group by.

[Frank_Hartmann]

Maybe like this:

Book_TEMP:
LOAD DocumentNumber,
     ORDERNUBER,
     TransactionType,
     AMOUNT
FROM [Book12.xlsx]
(ooxml, embedded labels, table is Sheet1);

left join
load  
ORDERNUBER,
if(count(ORDERNUBER) >1 ,1,0)  as Flag
Resident Book_TEMP
group by ORDERNUBER ;
NoConcatenate 
Load * Resident Book_TEMP Where AMOUNT>0 and Flag=1; DROP Table Book_TEMP;

 

[rajkumarb]

Solution

Book_TEMP:
LOAD DocumentNumber,
     ORDERNUBER,
     TransactionType,
     AMOUNT
FROM
[Book12.xlsx]
(ooxml, embedded labels, table is Sheet1);

left join(Book_TEMP)
load distinct ORDERNUBER,
if(count(ORDERNUBER)>1 and SUM(AMOUNT)>0,1,0)  as NEWFLAG
Resident Book_TEMP
group by ORDERNUBER;

NoConcatenate
Book:
Load *
resident Book_TEMP
where NEWFLAG = '1';

drop table Book_TEMP;