Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have a requirement to convert the excel into straight table. I am able to do that but there is a field which is a date field and when it's pulled in to application, it gets converted to number and despite of many trials I am not able to get the date from it. Please find attached the excel and my QVW in progress.
Basically, in qlikview I need the follwoing result
Field1 | Field2 | MonthYear | Data |
---|---|---|---|
a | b | 11 March 2014 | 23 |
a | b | 11 December 2014 | 45 |
c | d | 11 March 2014 | 67 |
c | d | 11 December 2014 | 89 |
Hi,
Try this.
Load Mnth as MonthName, Date(Num#(Mnth),'DD MMM YYYY') as MMMM Resident 1;
Its because the number which you are loading is in Text format.
Regards,
Kaushik Solanki
hi neha,
try to use the year(), month() in script,
beck
Hi,
Try this.
Load Mnth as MonthName, Date(Num#(Mnth),'DD MMM YYYY') as MMMM Resident 1;
Its because the number which you are loading is in Text format.
Regards,
Kaushik Solanki
Try adding Num#() to your resident load:
1:
CrossTable(Mnth, Data, 2)
LOAD * FROM
Date.xlsx
(ooxml, embedded labels, table is Sheet1);
LOAD Mnth as MonthName,
Date(Num#(Mnth),'DD MMM YYYY') as MMMM
Resident 1;
Thanks for the immediate response guys, it helped!
Num# is something I was missing there.
Thanks,
Neha
Hi neha sharma,
As Kaushik Solanki highlight, it seems the crosstable(..) function doesn't keep the original date format from excel during the table transformation..
In fact you can see the field 'Mnth' is on the left side of your list box, because it's interpret as a text field after the transformation of the function crosstable(..).
So you must use the 'Num#(Mnth)' function in the script to pass to Qlik the right format information.
So if you use the 'Date(Num#(Mnth),'DD MMM YYYY') as MMMM' and you put the 'MMMM' field in a list box you can see what you want achieve.
Regards,
Riccardo C.