Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading from Excel

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

3 Replies
neetu_singh
Partner
Partner

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.

or

can you share sample data.

Thanks

Not applicable
Author

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;

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand