Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@cdy1 Please share the data and expected output.
Data on excel with two tables one input and one output thank you
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)