multiple loads

[raadwiptec]

I have many excel files with the same starting name but for different month and years

my files are as following

invoice_01_2016

invoice_02_2016

..

invoice_01_2017 etc

For ex:

Select

... *

Applymap('test',invoice) as invoiceamount

from invoices_*.xls....

but here i have another file from example: invoiceamount_2016.xls , invoiceamount_2017.xls ,

test:

Mapping Load

invoice

invoice amount

from

invoiceamount_2016.xls

so here for using  an applymap ..i cannot use on all at once.. i need to load each year in seperate files..

thant means i can load invoice_* at a time.. but when applymap comes i need to divide the file and reiterate each year with each year applymaps..

is there an efficient way here?

[Siva_Sankar]

loop through to load all files from a folder and its subfolders?

[rahulpawarb]

Hello Raadwiptec,

Trust that you are doing great!

As a workaround you can make use of below script:

Map_Invoice_Amount:
Mapping LOAD
     SubField(FileBaseName(),'_', 2) & '|' & Invoice As InvoiceKey, 
     Amount
FROM
Data\InvoiceAmount_*.xlsx
(ooxml, embedded labels, table is Sheet1);

InvoiceData:
LOAD Invoice, 
     Date, 
     QTY,
     ApplyMap('Map_Invoice_Amount', SubField(FileBaseName(),'_', 3) & '|' & Invoice, 'No Invoice Amount') AS InvoiceAmount
FROM
Data\Invoice_*.xlsx
(ooxml, embedded labels, table is Sheet1);

Also refer the sample application attached herewith.

Hope this will be helpful.

Regards!

Rahul

[raadwiptec]

Hi Rahul,

My filebase name is invoice_13_01 .....invoice_15_01.......can you adjust the code for this... can you also tell me the logic behind 2 and 3 in your script..

[rahulpawarb]

Hello Raadwiptech,

I have used Subfield() function to extract substring components from a parent string field i.e. result of FileBaseName() function (This function will return the base file name without extension which is being loaded in QlikView); where the original record fields consist of two or more parts separated by a delimiter.

//Syntax
SubField(text, delimiter[, field_no ])
//Example
SubField(FileBaseName(),'_', 2)    \\ Returns the second field i.e. 2016 separated by _ delimiter if FileBaseName() function returns InvoiceAmount_2016
SubField(FileBaseName(),'_', 3)    \\ Returns the third field i.e. 2016 separated by _ delimiter if FileBaseName() function returns Invoice_01_2016

Please refer below modified code for your reference (I haven't tested it; please do the needful):

Map_Invoice_Amount: 
Mapping LOAD 
    Right(SubField(FileBaseName(),'_', 2),2) & '|' & Invoice As InvoiceKey, 
    Amount 
FROM 
Data\InvoiceAmount_*.xlsx 
(ooxml, embedded labels, table is Sheet1); 

InvoiceData: 
LOAD Invoice, 
    Date, 
    QTY, 
    ApplyMap('Map_Invoice_Amount', SubField(FileBaseName(),'_', 2) & '|' & Invoice, 'No Invoice Amount') AS InvoiceAmount 
FROM 
Data\Invoice_*.xlsx 
(ooxml, embedded labels, table is Sheet1);

Hope this will be helpful.

Regards!

Rahul

[Kushal_Chawda]

try like below

test:

Mapping Load

invoice

invoiceamount

FROM invoiceamount_*.xls

T1:

LOAD *,

Applymap('test',invoice) as invoiceamount

from invoice_*.xls