1 Reply Latest reply: Nov 2, 2017 2:11 PM by Sunny Talwar RSS

    Reformatting output from Crosstable loads

    Bradley Wilson

      Hi,

       

      I have been trying to import and concatenate together 4 different sales plans so that they can be analyzed in Qlik and I have run into a minor formatting issue.

       

      All of the plans are formatted similar to the below where each row has some attributes (SKU, Customer, Description, etc), and then there are a series of columns with Date headers showing when those amounts are expected.

       

      Attribute1Attribute2Attribute3MMM-YYMMM-YYMMM-YY
      Attribute 1Attribute 2Attribute 3AmountAmountAmount

       

      So I've successfully used crosstables to translate these sheets into tables and stitched them all together using concatenate.

       

      The problem that I am having now is that the Date headers were loaded as numbers.  And I can't figure out how to reformat them back into dates.  I've tried using date() and date#() in the crosstable load.  I've tried to use date() and date#() in the resident loads that I used to concatenate all the sheets together.  I've also tried using date functions in the vizualizations and nothing seems to be working.

       

      Nothing I try has been able to translate the ##### number format for dates into the MM/DD/YYYY format that I have set as default.

       

      Would anyone have ideas why I can't get this to work?  Is this a weird quirk of crosstables?  Are those values a special data type since they used to be field names?

       

      Thanks