Loading the values of xls as where condition in one of the load statement!

[qlikviewforum]

Hi All,

I have xls which contain field called "ID" and it has following values.

ID

ABC

XYZ

QRS

ASP

Basically I want to use the above values in WHERE condition of one of the load statement something like below.

Load * From data.qvd

Where Not Match( ID, 'ABC', 'XYZ', 'QRS', 'ASP')

Can someone help me out for the same please. It is little urgent!

[qlikviewforum]

Any updates from anyone on this?

[stabben23]

Hi,

Map:

Mapping LOAD

ID,

'1'

from xls

load*

from data.qvd

where applymap('Map', ID) <>1;

[qlikviewforum]

Hi Staffan,

Ok will try this and update you. By the way what is this '1'? I don't want to hard code the values in the WHERE condition. It should take all the values from the xls. I don't want to hard code all the values in the where condition as you did.

where applymap('Map', ID) <>1

By the way I am little doubtful whether this will work or not.

Hi All,

Is there any other way we can achieve this? Please help...

[stabben23]

Hi,

The Mapping Load will mark every row with '1'.

The applaymap in the where condition will look if there is a match from your xls. so no hard code need to be done. if your xls file will expand with new values you dont need to do noting. I havent hard cord any value?? in my condition. The <> means that all values in xls will be excluded in data.qvd.

[Anonymous]

Hi,

Please find the attached file for the sample. Is it this you want?

[qlikviewforum]

Sorry I didn't get you

I cannot expand the xls. I will have only one field only which the values that need to be excluded.

Any help from others?

[qlikviewforum]

Sorry Jerry this is not what I was looking for!

[qlikviewforum]

Basically I want to exclude the records while loading from QVD if the matching value is found in the xls. Please let me know if my question is not understandable!

[stabben23]

Hi, try this

xls:

LOAD * INLINE [
ID
ABC

  XYZ
QRS
ASP
];

Map:
Mapping Load
ID,
'1' as Flag
Resident xls;

fake_data_qvd:
LOAD * INLINE [
    ID, Value
    ABC, 10
    ABC, 20
    XYZ, 50
    HJK, 50
    OLP, 60
    OLP, 70
    ASP, 10
    PHP, 30
    ERT, 50
    QWE, 40
    QRS, 20
];

noconcatenate
Final_table:
LOAD
ID,
Value
Resident fake_data_qvd

Where ApplyMap('Map', ID) <> 1;

drop tables xls, fake_data_qvd;