Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
yogitamehta10
Creator
Creator

how to load multiple weekly files in script

HI Everyone,

i have a doubt related to file structure.

I have a excel file  i'm loading data from that file that file name has date embed in this .Book_22042016 , So i'm getting this file on weekly basis , like next file would be 29/04/2016 .

Every file has same type of data like in file we have item number ,  total 100 item number are there , these are fixed , no addition in that just the status of items are changing  So my dashbaord should show the current week data  and in another graph i will show last week and current week Progrees in status means i want to count stattus that is the progress.

how should i write my script so that it will capture all the weekly files and if i want i can show last week and current week data.

7 Replies
gardenierbi
Creator II
Creator II

Gysbert_Wassenaar

Perhaps like this:

ExcelData:

LOAD

     *,

     Date#(SubField(FileBaseName(),'_',-1),'DDMMYYYY') as FileDate

FROM

     Book*.xslx (ooxml, embedded labels, table is Sheet1)

     ;


talk is cheap, supply exceeds demand
yogitamehta10
Creator
Creator
Author

but then how i will i load my file on weekly basis automatically

Gysbert_Wassenaar

Together with all the other data. You can use the FileDate field to distinguish between the weeks.


talk is cheap, supply exceeds demand
yogitamehta10
Creator
Creator
Author

    week1 data

MAP_DATA_ITEM_NUMMAP_DATA_ITEM_NAMEMAP_DATA_ITEM_DESCRIPTION
D2001SPIDnever started
D2001start
D2001SPIDopen
D2001SPIDdevelopemnt
D2001SPIDopen

Week 2

MAP_DATA_ITEM_NUMMAP_DATA_ITEM_NAMEMAP_DATA_ITEM_DESCRIPTION
D2001SPIDstart
D2001in progress
D2001SPIDclose
D2001SPIDdevelopemnt in complete
D2001SPIDin progress

so check the type of data i have , i have three columns   item number is same everytime  just the status is changing.

so i need to calculate howmany are in progree , open , close this week and last week .

i'm getting these type of files.

how should i write my script.

file name is like Book_28042016

yogitamehta10
Creator
Creator
Author

so everytime new file come , how can qlik view automatically will laod  because file will be having differnt name as date will be changed while loading it will give me error

Gysbert_Wassenaar

Like this:

ExcelData:

LOAD

     MAP_DATA_ITEM_NUM,

     MAP_DATA_ITEM_NAME,

     MAP_DATA_ITEM_DESCRIPTION,

     Date#(SubField(FileBaseName(),'_',-1),'DDMMYYYY') as FileDate

FROM

     Book*.xslx (ooxml, embedded labels, table is Sheet1)

     ;


talk is cheap, supply exceeds demand