Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Any updates from anyone on this?
Hi,
Map:
Mapping LOAD
ID,
'1'
from xls
load*
from data.qvd
where applymap('Map', ID) <>1;
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...
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.
Hi,
Please find the attached file for the sample. Is it this you want?
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?
Sorry Jerry this is not what I was looking for!
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!
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;