topic Convert to Date in QlikView

Convert to Date

girish2195 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Please refer the attached attendance log file.

In this file I have numbers like (1,2,3,..... up to 31) as Dates and Sheet name is Jan - 17.

Now I have to convert those numbers into date(DD-MM-YYYY). I did Cross table to get all those numbers into a single date field.

Re: Convert to Date

marcus_sommer — Tue, 31 Jan 2017 12:48:00 GMT

The only way to read the sheet-names would be to load the excels per odbc, see: Loading Multiple Excel Sheets Dynamically along with file name and sheet name. There you add the sheet-name as a field and after a transforming of your data into "normal" data-structure per The Crosstable Load you could create a real date from it, probably per:

date(date#(Sheetname & '|' & Day, 'MMM - YY|DD')) as Date

It won't be very easy for you as starter but if you split the various parts - at first loading it without for-loop and without querying the sheetnames with a direct odbc-connect and quite similar the crosstable load to the excel as flatfile until it worked and you understand it - and then you could merge the different parts and add step by step more complexity.

- Marcus

Re: Convert to Date

buzzy996 — Tue, 31 Jan 2017 15:05:04 GMT

think the other way,wht's the probability load of ur application?

for example if u r running monthly -- u can simple take all the dates from the current month or the month & year from current month and append ur day from ur excel.