How to unpivot(transpose three columns) in data manager twice for the same table?

[Sai_Mohan]

Hi All,

I have used 'unpivot' from data manger to transpose the column.

For the same table, I need it to do twice, but in data manager, If i do it once, then second time, it is showing like 'undo unpivotting'.  Is there any option to do it in script?

How to do same unpivot for the below table?

Sample data:

I need to do cross table for this date.

First_date	Second_Date	Third_Date
24-06-2019	24-06-2019	24-06-2019
26-06-2019	26-06-2019	27-06-2019
28-06-2019	28-06-2019	28-06-2019
03-07-2019	01-07-2019	03-07-2019

The Resultant table should be like this:

Approver_Level_date	Approved_date
First_date	24-06-2019
Second_Date	24-06-2019
Third_Date	24-06-2019
First_date	26-06-2019
Second_Date	26-06-2019
Third_Date	27-06-2019
First_date	28-06-2019
Second_Date	28-06-2019
Third_Date	28-06-2019
First_date	03-07-2019
Second_Date	01-07-2019
Third_Date	03-07-2019

Thanks in advance!

[MayilVahanan · Accepted solution]

Hi @Sai_Mohan 

Try with CrossTable concept.Hope you 've some other field apart from these date also. for sample, I've included ID 

CrossTable(Approver_Level_date,Approved_date,1)
LOAD * INLINE [
ID,First_date, Second_Date, Third_Date
1,24-06-2019, 24-06-2019, 24-06-2019
2,26-06-2019, 26-06-2019, 27-06-2019
3,28-06-2019, 28-06-2019, 28-06-2019
4,03-07-2019, 01-07-2019, 03-07-2019
];

Ref: 

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/LoadData/work-w...

[Sai_Mohan]

Hi @MayilVahanan ,

Thanks for the help.

Those three columns(First, second and third date) is the columns of the table.

I have mentioned resultant column name by myself.

If those three columns comes from existing table, which has many columns with it, what will be the values given for crosstable.

For Example: 

1. CrossTable(First_date, ? )  //what will be the second parameter here.

Thanks in advance!

[MayilVahanan]

HI @Sai_Mohan 

Try like below

CrossTable(Approver_Level_date,Approved_date,1)

Load PrimaryKeyField ,First_date, Second_Date, Third_Date resident tableName;

In crossTable, you can give any names as per ur requirement. it will take column name of first_date, second_date & third_date as values in "Approver_Level_date" and the values of first_date, second_date & third_date fieds are falls under values in "Approved_date".

The number of qualifying columns can be stated as a third parameter to the crosstable prefix.

Please refer the link to understand about cross table

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/LoadData/work-w...

[Sai_Mohan]

Hi @MayilVahanan ,

If I give same like you said, I am getting error near crosstable:

Error: Unknown statement -  CrossTable(Approver_Level_date,Approved_date,1)

I have few columns,I will mention it.

Tablename:

Load

Id, Document, First_name, Last_name, Third_name, First_date,Second_date, Third_date,Document_type;

For this above table, where should i write crosstable function.   Should I write it above the Load statement or should I write seperate Load table and Do i need to mention only three columns with id field?

As you mentioned above, " In crossTable, you can give any names as per ur requirement. it will take column name of first_date, second_date & third_date as values in "Approver_Level_date" and the values of first_date, second_date & third_date fieds are falls under values in "Approved_date". ", - -----  How qlik will know those three columns will be taken in first parameter and their values will be taken in next parameter.

Thanks in Advance!

[MayilVahanan]

Try like this

Tablename:
CrossTable(Approver_Level_date,Approved_date,6)

Load

Id, Document, First_name, Last_name,
Third_name,Document_type,First_date,Second_date, Third_date from
ursourcefile;

[Sai_Mohan]

Thanks @MayilVahanan for the help!