Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
priyanka15
New Contributor II

Exists not working on a concatenated field

Hi,

Creating a field by concatenating 2 string fields but when trying to apply an "Exists" on the same in a succeeding load, the load fails with "Unable to find the field". While the exists worked fine for a singular numeric field (and maybe string too, but I haven't used it with a string field) but load fails in this scenario . Any suggestions as to how can that be resolved?

-Priyanka

1 Solution

Accepted Solutions
priyanka15
New Contributor II

Re: Exists not working on a concatenated field

Hi. Please find a sample data below with the script:

Table1                                  Table2
Item    |Price   |Month           Item    |Price  | Month
Book1 D73   May                  Book7  D99   Jun
Fan1   D85   May                  Fan3    D67   Jun
                                              Fan1   D85   Jun
                                              Fan5   D56   Jun

DB_May:
LOAD "Item"&'_'&"Price" AS "Item_Price";
SQL SELECT "Item",
"Price"
FROM ABC."May_Data"
WHERE "Item" IS NOT NULL
AND "Price"  IS NOT NULL;

DB_Jun:
LOAD "Item"&'_'&"Price" AS "Item_Price",
If(Exists("Item_Price"), 'Existing', 'New') as "Item_Price_Flag";
SQL SELECT "Item",
"Price"
FROM ABC."Jun_Data"
WHERE "Item" IS NOT NULL
AND "Price"  IS NOT NULL;

So basically I want to check if a combination of these 2 fields have occurred before in the previous month or not.

-Priyanka Negi

5 Replies
vishsaggi
Esteemed Contributor III

Re: Exists not working on a concatenated field

Can you share your script you are trying to run?

Khan_Mohammed
Honored Contributor II

Re: Exists not working on a concatenated field

Maybe try Preceding load as it cannot find the newly created field?


Table123:

//Preceding Load

Load *,

Full_Name;

//OriginalLoad
Load

a,

b,

c.....,

a & '  ' & b as Full_Name

From....

Where Not Exists (Full_Name);


priyanka15
New Contributor II

Re: Exists not working on a concatenated field

Hi. Please find a sample data below with the script:

Table1                                  Table2
Item    |Price   |Month           Item    |Price  | Month
Book1 D73   May                  Book7  D99   Jun
Fan1   D85   May                  Fan3    D67   Jun
                                              Fan1   D85   Jun
                                              Fan5   D56   Jun

DB_May:
LOAD "Item"&'_'&"Price" AS "Item_Price";
SQL SELECT "Item",
"Price"
FROM ABC."May_Data"
WHERE "Item" IS NOT NULL
AND "Price"  IS NOT NULL;

DB_Jun:
LOAD "Item"&'_'&"Price" AS "Item_Price",
If(Exists("Item_Price"), 'Existing', 'New') as "Item_Price_Flag";
SQL SELECT "Item",
"Price"
FROM ABC."Jun_Data"
WHERE "Item" IS NOT NULL
AND "Price"  IS NOT NULL;

So basically I want to check if a combination of these 2 fields have occurred before in the previous month or not.

-Priyanka Negi

priyanka15
New Contributor II

Re: Exists not working on a concatenated field

Please find the sample data and script above. And the field should be present as it is already loaded in a table preceding to the current load. Can you please check the script and tables above.

vishsaggi
Esteemed Contributor III

Re: Exists not working on a concatenated field

Are you looking something like below:

Capture.PNG

Table1:

LOAD *, Item&'_'&Price AS ItemPrice INLINE [

Item,Price,Month

Book1,  D73,   May

Fan1,   D85,   May

];

Join(Table1)

Table2:

LOAD *, IF(Exists(ItemPrice, ItemPrice), 'Exists', 'New') AS ItemFlag;

LOAD *, Item&'_'&Price AS ItemPrice INLINE [

Item,Price,Month

Book7,  D99,   Jun

Fan3,   D67,   Jun

Fan1,   D85,   Jun

Fan5,   D56,   Jun

];