Table Format issue

[qliklearnervir]

Hi,

I have a Table having column like below:

ID       Interested_City_X         Interested_City_Y          Interested_City_Z

----------------------------------------------------------------------------------------------------

1                        1                                     0                                     0

2                         0                                     1                                     0

3                         0                                    0                                      1

Instead of above i want like this:

ID                   CIty

1                      X

2                       Y

3                      Z

How it possible ,kindly suggest possible way.

[gautik92]

Load * inline

[

ID ,CIty

1 , X

2 , Y

3 ,  Z

];

[teiswamsler]

Hi Vir Vir

It sure can.

You can use the Crosstable funktion when you select your data to load in script wizard.

/Teis

[sunny_talwar]

You would need a CrossTable Load here:

Table:

LOAD * Inline [

ID,       Interested_City_X,         Interested_City_Y,          Interested_City_Z

1,                        1,                                     0,                                     0

2,                        0,                                     1,                                     0

3,                         0,                                    0,                                      1

];

CrossTable:

CrossTable(City, Data)

LOAD *

Resident Table;

NewTable:

LOAD SubField(City, '_', -1) as City,

  ID

Resident CrossTable

Where Data = 1;

DROP Tables Table, CrossTable;

[Gabriel]

Hi,

CrossTable is the function to use in this case

[maxgro · Accepted solution]

b:

load * inline [

ID    ,   Interested_City_X,         Interested_City_Y,          Interested_City_Z

1      ,                  1 ,                                    0,                                     0

2       ,                  0 ,                                    1,                                     0

3        ,                 0  ,                                  0  ,                                    1

];

t:

CrossTable(Interested_City_X, Data)

LOAD ID,

     Interested_City_X,

     Interested_City_Y,

     Interested_City_Z

Resident b;

DROP Table b;

z:

NoConcatenate LOAD

ID,

SubField(Interested_City_X, '_', 3) as City

Resident t where Data>0;

DROP Table t;