Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

qliklearnervir
New Contributor III

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.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Table Format issue

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;

5 Replies
gautik92
Valued Contributor III

Re: Table Format issue

Load * inline

[

ID ,CIty

1 , X

2 , Y

3 ,  Z

];

teiswamsler
Contributor II

Re: Table Format issue

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

Re: Table Format issue

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
Valued Contributor III

Re: Table Format issue

Hi,

CrossTable is the function to use in this case

MVP
MVP

Re: Table Format issue

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;