Load excel from a variable

[bharatkishore]

Hi All,

I have a excel in a path..

Can you please tell me how to load excel through a variable in qlikview script.

Thanks,

Bharat

[shwethaa]

Path is stored in excel???

[bharatkishore]

No...I have a excel in my system path..In that excel it has a field...I need to load that excel in qlikview script through a variable.

Hope i have explained clear. Please let me know if you need anything more..

[Frank_Hartmann]

Maybe like that:

Let vPath = 'C:\Users\admin\Desktop\yourExcelFile.csv';

Load *

From $(vPath)

(txt, utf8, embedded labels, delimiter is ';', msq);

[bharatkishore]

Thank you Hartmann.

I have give like this

let myfilter = '..\test\myexcel.xlsx';

LOAD

    data as Data,

FROM

..\path\test.qvd

(qvd) where match(data , '$(myfilter)');

But i am getting 0 records.. Can you please tell me where it is going wrong.

In excel i have Data field . In that i have date like 10,20

In the qvd also i have data field. There also i have data like 5,10,20, 30. But i need get data only the records present in excel.

Can you please tell me where i am doing wrong.

[Frank_Hartmann]

use Mapping Load. Try like this:

Map:
Mapping Load Data as data, 1 as Flag 
From ..\test\myexcel.xlsx;

Fact:
Load  
data
FROM
..\path\test.qvd (qvd) where ApplyMap('Map',data,'na')=1;

hope this helps

[bharatkishore]

Thank you for your reply.

I am already using mapping load to bring one field. So i am not sure whether i can use mapping one here. Please let me know if anything i am not clear.

Mapping

LOAD

    data as Data,

     material

FROM

..\path\test.qvd

(qvd) where match(data , '$(myfilter)');

[Frank_Hartmann]

if i understood you correctly then you want to load only values from your qvd which are also present in

your excel file?  then you will have to use the excel file as your Mapping table and restrict the fact table to only load the matched values by using applymatch in your where clause of the fact table!

[bharatkishore]

No... let me explain you bit more clearly.

I have an excel file where it has some standard numbers. Now i have a qvd where i will bring a field using mapping table into the original fact table.

The mapping table what i am using has same field name as in excel but it has some 1000 numbers. But  i need only those numbers present in excel.

Earlier i have done  by using Let statement and assigned all the numbers into a variable. But every time instead of doing manually i want to do automatic.

And i will be using 2 more qvd and using apply map i am bringing those fields and giving where condition where match of those numbers in excel.

Please let me know if you need anything more.