topic Re: calculation on the load script in QlikView

calculation on the load script

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

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

Re: calculation on the load script

sunny_talwar — Thu, 06 Dec 2018 12:37:51 GMT

What is the output you are hoping to get?

Re: calculation on the load script

rajkumarb — Thu, 06 Dec 2018 12:47:27 GMT

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

Re: calculation on the load script

lironbaram — Thu, 06 Dec 2018 12:48:45 GMT

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;

Re: calculation on the load script

jonathandienst — Thu, 06 Dec 2018 12:52:48 GMT

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.

Re: calculation on the load script

Frank_Hartmann — Thu, 06 Dec 2018 12:56:22 GMT

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;

Re: calculation on the load script

rajkumarb — Thu, 06 Dec 2018 13:36:14 GMT

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;