topic Re: Combine Two Fields As One in QlikView

Combine Two Fields As One

Thu, 04 Oct 2012 14:56:39 GMT

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

];

Re: Combine Two Fields As One

Thu, 04 Oct 2012 15:11:46 GMT

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;

Re: Combine Two Fields As One

Thu, 04 Oct 2012 15:18:33 GMT

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.

Re: Combine Two Fields As One

Thu, 04 Oct 2012 15:29:11 GMT

What about...

LOAD

sum(Qty1) as Qty1

sum(Qty2) as Qty2

sum(Qty3) as Qty3

RESIDENT Test;

Re: Combine Two Fields As One

Thu, 04 Oct 2012 16:59:34 GMT

Hi,

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

Thanks,

-Khaled

Re: Combine Two Fields As One

kmn — Thu, 04 Oct 2012 17:38:15 GMT

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

Re: Combine Two Fields As One

Thu, 04 Oct 2012 17:40:04 GMT

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.

Re: Combine Two Fields As One

jagan — Fri, 05 Oct 2012 11:13:28 GMT

Hi,

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

Regards,

Jagan.

Re: Combine Two Fields As One

Fri, 05 Oct 2012 19:30:37 GMT

That's it!

Thank you all.

Regards,

-Khaled