Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ramshoney
Contributor

Regarding Converting Integer to MMM YYYY format

I have created one pivot/Crosstab file in the excel with the below screenshot. when i import the file into the Load editor i'm able to see the below screen.

SourceFile Format:

Load Editor format :


i'm unable to find the functions of converting the ( 41640,41671,.........) values to the above excel MON-YYYY format in my Table.

Where i want to get the below format after loading.

Need some Date conversation functions to work on these kind of conversations.

Thanks

Rams.

1 Solution

Accepted Solutions

Re: Regarding Converting Integer to MMM YYYY format

Add a resident load after your above script like this

FinalTable:

NoConcatenate

LOAD Product,

     Date(Num#(Month), 'MMM YYYY') as Month,

     Sales

Resident ....;

DROP Table ....;

10 Replies

Re: Regarding Converting Integer to MMM YYYY format

Add a resident load after your above script like this

FinalTable:

NoConcatenate

LOAD Product,

     Date(Num#(Month), 'MMM YYYY') as Month,

     Sales

Resident ....;

DROP Table ....;

Re: Regarding Converting Integer to MMM YYYY format

I assumed you are already using CrossTable load... but it seems you are not... look here on how to use crosstable load

The Crosstable Load

Re: Regarding Converting Integer to MMM YYYY format

After using crosstable load date columns converted into num dates then you have to use Date and Date#

Then try this after Cross table load

Final:

NoConcatenate

LOAD

Product,

Date(Date#(Num#(Month),'MMM YYYY')'MMM YYYY') as Month,

Sales

Resident CrosstableName;

DROP Table CrosstableName;

Khan_Mohammed
Honored Contributor II

Re: Regarding Converting Integer to MMM YYYY format

As Sunny mentioned maybe below one should work??

Table:

CrossTable (MonthYear , Sales,1) 

LOAD Product,

     [Jan-14],

     [Feb-14],

     [Mar-16],

     [Apr-13]

     Etc....

FROM......;

ramshoney
Contributor

Re: Regarding Converting Integer to MMM YYYY format

Sunny, Thanks for your kind response.

This one works perfect "Date(Num#(Month), 'MMM YYYY') as Month"

Thanks,

Rams

ramshoney
Contributor

Re: Regarding Converting Integer to MMM YYYY format

Anand, Thanks for your kind response.

When i try with the "Date(Date#(Num#(Month),'MMM YYYY')',MMM YYYY') as Month" I'm getting nulls in the field.

As Sunny mentioned ""Date(Num#(Month), 'MMM YYYY') as Month"", This is working fine as my expectation.

Thanks,

Rams.

ramshoney
Contributor

Re: Regarding Converting Integer to MMM YYYY format

Aehman, Thanks for your kind reply.

Ya, Sunny mentioned is working.

Re: Regarding Converting Integer to MMM YYYY format

Can you provide a sample to have a look m code is working as usual properly with this code, sunny code is also working may be some format issue.

ramshoney
Contributor

Re: Regarding Converting Integer to MMM YYYY format

Anand, please see the below Code.

Output:


--Rams

Community Browser