Forum Discussion

Bhoobala_P's avatar
Bhoobala_P
Frequent Visitor
9 years ago

Use Column Name as Filter

Guys,   I have a dataset like this: Customer ID onsite/offshore Region 01-01-2011 01-02-2011 01-03-2011 01-04-2011 Customer1 onsite    Kuwait 200000 200000 200000 300000 Custo...
Need Help
Sean's avatar
Sean
Community Champion
9 years ago

Bhoobala_P  No need to Duplicate the table

In the Query Editor

1) Transform tab => Use First Row as Headers (if necessary)

2) select Customer ID, onsite/offshore and Region Columns => Unpivot Other Columns
3) Rename New Columns (if necessary) Attribute to Date

4) Close and Apply

 

Here's the result...

 

 

And...

 

 

Hope this helps.

Good Luck! :smileyhappy:

  • Bhoobala_P's avatar
    Bhoobala_P
    Frequent Visitor
    9 years ago

    Dear Sean,

     

    Thanks, that really helped me. However in the first step, i.e., when I load the data and selected 'Use First Row as Header', it still converts the date value into default text(Column5, Column6, etc).

     

    Is there an option to make the date appear as column name as such, when I select 'Use First Row as Header'??

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Bhoobala_P,

      When you select 'Use First Row as Header', it is impossible to make the date column heading as date type, we are only able to change type for values in one column.

      After you Unpivot columns, you are able to change the type of date value in Attribute column to Date type(select the whole Attribute column and right click on it, you will get the following screenshot).

       


      Thanks,
      Lydia Zhang

      • Bhoobala_P's avatar
        Bhoobala_P
        Frequent Visitor
        9 years ago

        Dear Lydia,

         

        Thanks for the reply. Let me give some clarity to the picture. This is the sample data:

        PracticeEmployeeActivitity01-04-201101-05-201101-06-2011
        Project1Employee1Travel500005000050000
        Project1Employee2Travel500105001050010
        Project1Employee3Travel500205002050020
        Project1Employee4Travel500305003050030

         

        Pls refer to the snapshots below:

        1. Loading the data2. Choosing first row as header3. Result (issue)

        Now, how can we make the date appear as such, after making the first row as header in the query editor???

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hello,

     

    because of this solution data size will increase let's say we have 1 billion records if we unpivot it then it will convert in 4 Billion. This is not feasible. Actually, I am also facing this issue. Can you suggest me a better solution?

     

    Thanks in advance