Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

bharatkishore
Contributor III

Load excel from a variable

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

8 Replies
Highlighted
shwethaa
New Contributor III

Re: Load excel from a variable

Path is stored in excel???

bharatkishore
Contributor III

Re: Load excel from a variable

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
Honored Contributor II

Re: Load excel from a variable

Maybe like that:

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

Load *

From $(vPath)

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

bharatkishore
Contributor III

Re: Load excel from a variable

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
Honored Contributor II

Re: Load excel from a variable

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
Contributor III

Re: Load excel from a variable

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
Honored Contributor II

Re: Load excel from a variable

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
Contributor III

Re: Load excel from a variable

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.