topic Load excel from a variable in QlikView

Load excel from a variable

bharatkishore — Fri, 05 Oct 2018 07:23:15 GMT

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

Re: Load excel from a variable

shwethaa — Fri, 05 Oct 2018 07:37:59 GMT

Path is stored in excel???

Re: Load excel from a variable

bharatkishore — Fri, 05 Oct 2018 07:44:33 GMT

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..

Re: Load excel from a variable

Frank_Hartmann — Fri, 05 Oct 2018 07:52:36 GMT

Maybe like that:

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

Load *

From $(vPath)

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

Re: Load excel from a variable

bharatkishore — Fri, 05 Oct 2018 08:09:58 GMT

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.

Re: Load excel from a variable

Frank_Hartmann — Fri, 05 Oct 2018 08:27:11 GMT

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

Re: Load excel from a variable

bharatkishore — Fri, 05 Oct 2018 08:34:35 GMT

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)');

Re: Load excel from a variable

Frank_Hartmann — Fri, 05 Oct 2018 08:45:20 GMT

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!

Re: Load excel from a variable

bharatkishore — Fri, 05 Oct 2018 09:48:11 GMT

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.