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