Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have two tables:
[Invoice]:
Load
'Invoice Number',
'Product Key'
FROM qvd
and
[Product Table]:
Load
'Product Key',
'Class'
FROM qvd
If there are some Products Key values in Invoice that do not have corresponding Product Key rows in Product Table, then Class will display as '-' in a table with fields Invoice Number, and Class.
My end goal is to produce a report that shows me a list of all Product Keys that exist in Invoice, but not in Product Table. I've been trying to go about it with a table that contains Product Key, and Class, and creating a column that is =If(IsNull(Class), 1, 0) expecting a 1 to show if there is no row in Product Table matching the Product Key, however it evaluates to '-' if Class doesn't exist, and 0 if it does, so I'm unable to filter by this value at all.
Is there a way for me to do this? Or, alternatively, a way for me to set a default value on Class in the data load, so that if Class doesn't exist, displays as "Unmapped" or something?
Cheers, Tim
May be try like below
Map_Product_class:
Mapping
Load
'Product Key',
'Class'
FROM qvd;
[Invoice]:
Load
'Invoice Number',
'Product Key'
Applymap('Map_Product_class', 'Product Key','Not Available in Product class') as Product_Key_Class
FROM qvd
try
if(isnull([Field]) OR [Field]= '','Yes','No') as field
or
if(len(trim(Field))=0,0,1) as [NewField]
or
If(Len(Trim(field)) > 0, 1, 0) as [NewField]
May be try like below
Map_Product_class:
Mapping
Load
'Product Key',
'Class'
FROM qvd;
[Invoice]:
Load
'Invoice Number',
'Product Key'
Applymap('Map_Product_class', 'Product Key','Not Available in Product class') as Product_Key_Class
FROM qvd