topic Re: load excel field as a variable in QlikView

load excel field as a variable

bharatkishore — Fri, 05 Oct 2018 10:45:19 GMT

Hi All,

stalwar1‌

I have an excel with a field.

Now in qlikview i want to load that field from excel and assign to a variable. Is it is possible?

Thanks,

Bharat

Re: load excel field as a variable

sunny_talwar — Fri, 05 Oct 2018 11:34:27 GMT

It should be fairly easy, but how many values do you have in the field?

Re: load excel field as a variable

bharatkishore — Fri, 05 Oct 2018 11:42:20 GMT

94 values Sunny Bhai..

Re: load excel field as a variable

sunny_talwar — Fri, 05 Oct 2018 11:43:19 GMT

So, you want 94 variables?

Re: load excel field as a variable

bharatkishore — Fri, 05 Oct 2018 11:54:04 GMT

No Sunny Bhai..

Actually the thing is let me tell me clearly

I have an excel file where it has some standard numbers(94). 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 sunny bhai if i am not clear

Re: load excel field as a variable

daniellial — Fri, 05 Oct 2018 11:57:58 GMT

HI,

Maybe you can create mapping table direct from the excel file.

Re: load excel field as a variable

bharatkishore — Fri, 05 Oct 2018 11:59:34 GMT

As of now i have give like this :

LET myFilter = '1, 2,

10,

11,

12,............(all 94 numbers)

Mapping

LOAD Code,

group

FROM

[..\Test.qvd]

(qvd)Where WILDMATCH(Code, $(myFilter));

Re: load excel field as a variable

sunny_talwar — Fri, 05 Oct 2018 12:04:03 GMT

Why don't you use Where Exist.

Load Field as Code

From Excel;

FactTable:

LOAD ....,

Code

FROM ....

Where Exists (Code);

Re: load excel field as a variable

bharatkishore — Fri, 05 Oct 2018 12:09:10 GMT

I am using a mapping table Sunny Bhai..Will that work there? The code what i have written

Let myfilter = 1,2,3,4,.....94;

MappingTable5:

Mapping

LOAD

code as Code,

CodeDescription

FROM

[..\Test.qvd]

(qvd)Where WILDMATCH(code, $(myfilter));

FinalTable:

load Code,

ApplyMap('MappingTable5', Code, Null()) as CodeDescription

resident main table

Re: load excel field as a variable

sunny_talwar — Fri, 05 Oct 2018 12:24:23 GMT

I am not sure what you are trying to get to... but may be this

Temp:

LOAD code

From Excel;

Temp2:

LOAD code as Code,

CodeDescription

FROM [..\Test.qvd] (qvd)

Where Exists(code);

MappingTable5:

Mapping

LOAD *

Resident Temp;

DROP Tables Temp, Temp2;

FinalTable:

load Code,

ApplyMap('MappingTable5', Code, Null()) as CodeDescription

resident main table;

You can also do a Right Join or Left Join based on the order of the table

Re: load excel field as a variable

bharatkishore — Fri, 05 Oct 2018 12:29:00 GMT

Thank you so much Sunny Bhai...Really thank you..