Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
qliklearnervir
Creator
Creator

Table Format issue

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

View solution in original post

5 Replies
gautik92
Specialist III
Specialist III

Load * inline

[

ID ,CIty

1 , X

2 , Y

3 ,  Z

];

teiswamsler
Partner - Creator III
Partner - Creator III

Crosstable.PNG

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;


Capture.PNG

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

CrossTable is the function to use in this case

maxgro
MVP
MVP

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;