topic Re: combining two fields to one in QlikView

combining two fields to one

Thu, 25 Oct 2012 19:15:36 GMT

Hi,

My requirement is I have two fields X,Y. X field has values A,B and Y field has value C..now I want to create a new field Z with values A,B,C. My intention is to create graphs on combined values A,B and C.

Any help is highly appreciated.

Re: combining two fields to one

MayilVahanan — Thu, 25 Oct 2012 19:34:53 GMT

Try like this

Test:

LOAD * Inline

[

X,Y

A,C

B,C

];

Load SubField(Z,',') as Z;

LOAD X&','&Y as Z Resident Test;

Re: combining two fields to one

Thu, 25 Oct 2012 19:35:01 GMT

not clear, how is your data file ? can you put an example.

Re: combining two fields to one

CELAMBARASAN — Thu, 25 Oct 2012 19:36:17 GMT

X,Y,Z are from same table?

Re: combining two fields to one

CELAMBARASAN — Thu, 25 Oct 2012 19:43:08 GMT

Hi Mayil

1. Your result will have duplicates if both the field has same values or value appears more than once.

2. Why you need to combine and then separately need to apply subfield over it?

Simply you can do this right?

Test:

LOAD * Inline

[

X,Y

A,C

B,C

];

LOAD Distinct SubField(X&','&Y,',') as Z Resident Test;

Re: combining two fields to one

Thu, 25 Oct 2012 20:00:57 GMT

X,Y columns are from spreadsheet...

Re: combining two fields to one

Thu, 25 Oct 2012 20:13:25 GMT

these columns are coming from excel

Re: combining two fields to one

MayilVahanan — Thu, 25 Oct 2012 20:18:17 GMT

Test:

LOAD X,Y from filename.xls;

LOAD Distinct SubField(X&','&Y,',') as Z Resident Test;

is not work as you expected?

Re: combining two fields to one

Thu, 25 Oct 2012 20:32:32 GMT

Thank you Mayil Vahanan Ramasamy , i think your solution worked..but in 2nd column there are various value I want to extract only particular value C..consider example two columns State and region

Ca West

NY East

TX South

I need new column Z with values

West..

so, if put the where condition in 2nd column

LOAD Distinct SubField(X&','&Y,',') as Z Resident Test

where Y='West' I'm getting only two values for Z

West

any help is highly appreciated..

Re: combining two fields to one

CELAMBARASAN — Thu, 25 Oct 2012 20:41:01 GMT

Use Simply as

Table1:

Load State,Region,State as NewField

from excelSource;

Load * Inline [

NewField

West

];

Edit:

If you need Regions under State starts with c then

Load State as NewField

Resident

Table1 Where WildMatch(State, 'C*');

Hope it helps

Re: combining two fields to one

MayilVahanan — Thu, 25 Oct 2012 20:42:27 GMT

Try like this

test:

LOAD * Inline

[

State,region

Ca,West

NY,East

TX,South

];

Load if(wildmatch(State,'C*'),SubField(State&','&region,','),State) as Z Resident test;