Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ....;
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 ....;
I assumed you are already using CrossTable load... but it seems you are not... look here on how to use crosstable load
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;
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......;
Sunny, Thanks for your kind response.
This one works perfect "Date(Num#(Month), 'MMM YYYY') as Month"
Thanks,
Rams
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.
Aehman, Thanks for your kind reply.
Ya, Sunny mentioned is working.
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.
Anand, please see the below Code.
Output:
--Rams