topic Re: Loading from Excel in QlikView

Loading from Excel

Wed, 06 Nov 2013 08:04:49 GMT

Hi all,

We are loading from different Excel sheets each month, which means that we have 12 different files for a year. Some of the sheets includes historical data. Somehow the historical data changes from file to file, I know this is really crappy data quality, but it will take ages to sort this out. Is there anyway I can tell Qlik which file that is the newest one? Maybe the order by function can help us in some way?

Any tips or solutions will be highly appreciated,

thanks

Re: Loading from Excel

neetu_singh — Wed, 06 Nov 2013 08:15:17 GMT

Hi Jon,

Is there any date field in your data. as if there is a date field then we can apply the condition that where last reload date is less than the date(that date which lies in your data), then pick only that data.

can you share sample data.

Thanks

Re: Loading from Excel

Wed, 06 Nov 2013 08:42:37 GMT

Thanks,

I have to warn you, we have made a "strange" logic that enables us to load the different excel sheets.

Attached you can find a sample file containing the sheets that have historically data. I am using the below script to load the ibbymonthindex sheet. The "Month" is growing right on the excel sheet from month to month. "Opening_date" is not changing.

Temp:

CrossTable(Scenerio,Value, 4)
LOAD
FileName() as FilenameFakta,
*
FROM
[..\Data\status*]
(ooxml, embedded labels, table is IbByMonthIndex);

Fact:
load
     Club,
     dep,
     Scenerio,
     'Month Development Members' as Measure,
     Date(Date#(opening_date)) as Datee,
     Value,
     'ravIbByMonthIndex' as Source,
     filename() as FilenameFakta

   Resident Temp;
   DROP Table Temp;

Re: Loading from Excel

Gysbert_Wassenaar — Wed, 06 Nov 2013 12:28:03 GMT

Maybe the filetime() function:

filetime([ filename ])

Returns a timestamp for the date and time of the last modification of the file filename. If no filename is specified, the function will refer to the currently read table file.

Examples:

filetime( 'xyz.xls' )

Will return the timestamp of the last modification of the file xyz.xls.

Load *, filetime() as X from abc.txt ;

Will return the date and time of the last modification of the file (abc.txt) as a timestamp in field X in each record read.