Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cdy1
Contributor II
Contributor II

Script Load table

I´ve got the follwing data 

Load Inline [Name, 

                        N1,

                        N2,

                        N3,

                         N4];

Table 1:

Load

Date,

Name ,

Operation, 

Amount, 

From an excel file; 

Table 1 data contains different opeartion numbers, but these numbres can be repeated so for example N1 can have same opeartion number as N2 etc.  I want to create a new table that contains for every date and operation the four names and if the there is an amount associated to that name , operation and date, then it will show the amount but if that name wasn´t associated to that operation number and date at first then it will show  1. 

 

Labels (1)
3 Replies
Qlik1_User1
Specialist
Specialist

@cdy1 Please share the data and expected output.

cdy1
Contributor II
Contributor II
Author

Data on excel with two tables one input and one output thank you

MayilVahanan

Hi @cdy1 

Try like below

Temp:
LOAD * INLINE [
Date, Order ID, Type Order, Person ID, Amount
4/3/2020, 2899, 104, 20, 172616.00
4/3/2020, 1847, 104, 21, 56125
4/3/2020, 1524, 104, 27, 53805
4/3/2020, 1982, 104, 30, 143425
9/3/2020, 2495, 107, 21, 111768
14/3/2020, 1759, 107, 27, 112475
17/3/2020, 2784, 107, 30, 17756
19/3/2020, 1080, 107, 27, 96137
20/3/2020, 1807, 107, 30, 158682
20/3/2020, 2121, 500, 21, 98858
20/3/2020, 2662, 500, 27, 40759
20/3/2020, 2397, 550, 21, 145507
20/3/2020, 2166, 550, 27, 110504
4/4/2020, 1303, 550, 30, 122683
5/4/2020, 2160, 550, 21, 176811
];

Temp1:
Load [Type Order] Resident Temp;
Join
Load [Person ID] Resident Temp;

Join(Temp)
Load [Type Order], [Person ID] Resident Temp1;

NoConcatenate
Load Distinct * Resident Temp;

DROP Table Temp1, Temp;

 

O/P:

For [Total Amount By Type], exp is Sum(Total<[Type Order]> Amount)

MayilVahanan_1-1622877009068.png

 

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Community Browser