Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rajkumarb
Creator II
Creator II

calculation on the load script

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

DocumentNumberORDERNUBERTransactionTypeAMOUNT
1012A101P110
1022A102P120
1032A103P130
1042A104P140
1052A105P150
1062A106P160
1072A107P170
1082A108P180
1092A109P190
1102A110P1100
1112A111P1110
1122A112P1120
1132A101P2-10
1142A102P2-20
1152A103P2-30
1162A104P2-40
1172A105P2-30
1182A106P2-20
1 Solution

Accepted Solutions
lironbaram
Partner
Partner

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;

View solution in original post

6 Replies
sunny_talwar

What is the output you are hoping to get?
rajkumarb
Creator II
Creator II
Author

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
Partner
Partner

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;

View solution in original post

jonathandienst
Partner
Partner

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.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Frank_Hartmann
Master II
Master II

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;

 image.png

rajkumarb
Creator II
Creator II
Author

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;