topic Script Load table in Search the Community

Script Load table

cdy1 — Thu, 03 Jun 2021 02:24:12 GMT

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.

Re: Script Load table

Qlik1_User1 — Thu, 03 Jun 2021 07:23:12 GMT

@cdy1 Please share the data and expected output.

Re: Script Load table

cdy1 — Sat, 05 Jun 2021 02:23:35 GMT

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

Re: Script Load table

MayilVahanan — Sat, 05 Jun 2021 07:10:20 GMT

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)