Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Community,
I am trying to combine multiple fields (Qty1,Qty2,Qty3) in load sript as one field (named "Quantities"). So, my resultant column "Quantities" would be a list having values like Qty1,Qty2 and Qty3 and not the contents of Qty(n).
I cannot use a cross table, since there are multiple activities per each date in my actual script and using a crosstable will explode the number of records and not give me the actual number of activities per date.
Is there an easier way to do this?
Please see the test script below: (sample document attached)
Test:
LOAD * Inline
[
Date,ID,Name,Qty1,Qty2,Qty3
10/1/2012,101,Adam,12,10,5
10/2/2012,102,Chris,1,5,9
10/2/2012,103,Bella,8,9,7
10/3/2012,104,John,7,15,5
];
Hi,
Check attached file, hope that's what you are expecting.
Regards,
Jagan.
Is this what your're looking for?
Test:
LOAD * Inline
[
Date,ID,Name,Qty1,Qty2,Qty3
10/1/2012,101,Adam,12,10,5
10/2/2012,102,Chris,1,5,9
10/2/2012,103,Bella,8,9,7
10/3/2012,104,John,7,15,5
];
LOAD
Date,
Qty1 + Qty2 + Qty3 as totalQty
Resident Test;
Hi,
Thanks for your prompt response, Brian.
However, I am not looking to add the sum of quantities to create a new field.
The requirement is to get field names Qty1, Qty2, Qty3 as values in a new field called Quantites.
In short, I need a new column that looks like this:
Quantities:
Qty1
Qty2
Qty3
Thanks,
-Khaled.
What about...
LOAD
sum(Qty1) as Qty1
sum(Qty2) as Qty2
sum(Qty3) as Qty3
RESIDENT Test;
Hi,
But, that will give me the sum of individual quantities as a field for each quantity, which is not my requirement.
Thanks,
-Khaled
Give this a try:
Test:
LOAD * Inline
[
Date,ID,Name,Qty1,Qty2,Qty3
10/1/2012,101,Adam,12,10,5
10/2/2012,102,Chris,1,5,9
10/2/2012,103,Bella,8,9,7
10/3/2012,104,John,7,15,5
];
T1_Temp:
load distinct
Concat(Qty3,';') as Qty_temp,
'Qty3' as Quantities
Resident Test;;
Concatenate
load distinct
Concat(Qty2,';') as Qty_temp,
'Qty2' as Quantities
Resident Test;;
Concatenate
load distinct
Concat(Qty1,';') as Qty_temp,
'Qty1' as Quantities
Resident Test;
Drop table Test;
T1:
Load Distinct
subfield(Qty_temp,';') as Value,
Quantities
Resident T1_Temp;
Drop table T1_Temp;
/Kmn
You could do an Inline load as:
Qty:
Load * Inline [
Quantities
Qty1
Qty2
Qty3
];
to get the 3 Qty(n) fields to show up as Quantities.
Hi,
Check attached file, hope that's what you are expecting.
Regards,
Jagan.
That's it!
Thank you all.
Regards,
-Khaled