Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai_Mohan
Contributor III
Contributor III

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

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_dateSecond_DateThird_Date
24-06-201924-06-201924-06-2019
26-06-201926-06-201927-06-2019
28-06-201928-06-201928-06-2019
03-07-201901-07-201903-07-2019

 

The Resultant table should be like this:

Approver_Level_dateApproved_date
First_date24-06-2019
Second_Date24-06-2019
Third_Date24-06-2019
First_date26-06-2019
Second_Date26-06-2019
Third_Date27-06-2019
First_date28-06-2019
Second_Date28-06-2019
Third_Date28-06-2019
First_date03-07-2019
Second_Date01-07-2019
Third_Date03-07-2019

 

Thanks in advance!

 

Labels (4)
1 Solution

Accepted Solutions
MayilVahanan

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...

Thanks & Regards,
Mayil Vahanan R

View solution in original post

6 Replies
MayilVahanan

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...

Thanks & Regards,
Mayil Vahanan R
Sai_Mohan
Contributor III
Contributor III
Author

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...

 

Thanks & Regards,
Mayil Vahanan R
Sai_Mohan
Contributor III
Contributor III
Author

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;
Thanks & Regards,
Mayil Vahanan R
Sai_Mohan
Contributor III
Contributor III
Author

Thanks @MayilVahanan for the help!