load excel field as a variable

[bharatkishore]

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

[sunny_talwar]

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

[bharatkishore]

94 values Sunny Bhai..

[sunny_talwar]

So, you want 94 variables?

[bharatkishore]

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

[daniellial]

HI,

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

[bharatkishore]

As of now i have give like this :

LET myFilter = '1, 2,

3,

4,

5,

6,

7,

8,

9,

10,

11,

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

Mapping

LOAD Code,  

     group

FROM

[..\Test.qvd]

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

[sunny_talwar]

Why don't you use Where Exist.

Load Field as Code

From Excel;

FactTable:

LOAD ....,

     Code

FROM ....

Where Exists (Code);

[bharatkishore]

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

[sunny_talwar · Accepted solution]

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