Combine Two Fields As One

[#1]

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

];

[#2]

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;

[#3]

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.

[#4]

What about...

LOAD

sum(Qty1) as Qty1

sum(Qty2) as Qty2

sum(Qty3) as Qty3

RESIDENT Test;    

[#5]

Hi,

But, that will give me the sum of individual quantities as a field for each quantity, which is not my requirement.

Thanks,

-Khaled

[kmn]

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

[#7]

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.

[jagan · Accepted solution]

Hi,

Check attached file, hope that's what you are expecting.

Regards,

Jagan.

[#9]

That's it!

Thank you all.

Regards,

-Khaled