Pivot in Script?

[Anonymous]

Is there a way to pivot in your script? I have the below:

          ID      Name                Group                Address

           1      John                NewYork                100 Center Ave

           1      John                New Jersey              100 Center Ave

               2      Adam              New York                900 Center Ave

           3      Tiffany           Main                          123 Center Ave

What I would like to be able to do is Convert the Group Rows into Columns. I have about 40 of them and there will be more groups in the future and the name field could potentially be affliated to all groups.

Results:

ID      Name           NewYork           New Jersey           Main           Address

1           John                x                     x                                              100 Center Ave

2           Adam                                    x                                                  9 00 Center Ave

3           Tiffany                                                                  x                 123 Center Ave

I was thinking about ApplyMap function but I do not know if that would be the best approach and not really sure of the syntax for ApplyMap. Help would be appreciated as I'm fairly new to Qlikview, Thanks

[Final Table]:

Load

ID

,Name

,Group

, other columns...

[Miguel_Angel_Baeyens]

Hi John,

You need to use CROSSTABLE here:

CROSSTABLE (3, CityName, CityValue) LOAD *

FROM Excelfile....

3 means the number of columns you want to keep as such (ID, Name, Address)

CityName is the name of the columns (New York, New Jersey, Main)

CityValues is Y/N or whatever the value is (x, null, etc)

Miguel

[Anonymous]

Not sure this is the correct answer. Is that the correct format for the Crosstable? I'm not getting a desired outcome

[Anonymous]

This is what I have so far...

     [Final Table]:

     CrossTable(Group,x,2)

LOAD  ID ,Name  ,Address       

RESIDENT

[temp];
DROP TABLE [temp]

     

[oknotsen]

Making a pivot table in the script is not possible. There is no such thing as a pivot table in your data model. All data will end up in "regular" tables.

The code examples they gave you are if you are trying to load data from a source file that is layout as a pivot table (aka cross table). To load those, use the example given.

After loading the data, the group column will be a dimension which you can than use in creating your pivot table in your front end.

Hope that clarifies it a bit.

[Josh_Good]

John,

If you use a pivot table and the expression below I believe you will get what you are looking for

=if(Count(ID) >= 1, 'X', ' ')

You will need to be on Qlik Sense 1.1 to use the pivot table.

-Josh

Qlik

[jagan]

Hi,

Try Generic load or you can use Pivot table.

The Generic Load

Regards,

Jagan.

[Anonymous]

Solution: Thanks for the feedback everyone. It sounds like what I would like to do cannot my be created in Qlikview. I will create a seperate Pivot query in SQL and bring over and join to my data,

[jagan]

Hi,

This is possible by using Generic load or you can try pivoting in SQL itself.  Refer this link below

The Generic Load

Regards

Jagan.

[#10]

I don't know why your answer wasn't flagged as the correct answer. I tried it an it worked beautifully.