topic Re: Multiple Field Exist (Cheking if values exist in different fields at the same row) in QlikView

Multiple Field Exist (Cheking if values exist in different fields at the same row)

Anonymous — Fri, 18 Nov 2016 09:04:50 GMT

Hello, i have some issue trying to simplifying my code with the function Exists(). Let me first explain where my data come from with an example. I have to data excel sources.

On the first one there are the fields Date , ProductNo, ProductPrice like this

Date, ProductNo, ProductPrice

201401, A1, 100

201401, A2, 120

201402, A2, 121

201403, A1, 110

201403, A2, 121

The second contains Date, ProductNo, NSoldProd:

Date, ProductNo, NSoldProd

201401, A1, 10

201401, A2, 15

201402, A1, 8

201402, A2, 13

201403, A1, 30

201403, A2, 18

In order to concatenate it properly and do not have a $Syn Table (because, as far as I know, the Synthetic tables usually gives problems), I create my Own PrimaryKey using the fields that share both data bases (Date, ProdNo). Then, I load it like:

Price:

LOAD Date,

    ProductNo,

    Date & ProductNo as PrimKey,

    ProductPrice

FROM

[DB-Price.xlsx]

(ooxml, embedded labels, table is Sheet1);

Sales:

LOAD Date & ProductNo as PrimKey,

    NSoldProd

FROM

[DB-Sales.xlsx]

(ooxml, embedded labels, table is Sheet1);

The way the data apears in QlikView is like this:

Date	ProductNo	=Sum(NSoldProd)
201401	A1	10
201401	A2	15
201402	A2	13
201403	A1	30
201403	A2	18
-	-	8

The explanation of this is peaty easy if you know that QlikView search for matches with the PrimaryKey (on both tables. The point is that the bold field of the second table contains the field 201402A1 which does not exist on the firs table where only exists the 201402A2. I want to filter the data and take only the fields that exist in both fields. The function that is supposed to do this work is exists(). The problem is that when I use exists(Date) (or when I use exists(ProductNo) ), QlikView only check if this Date ( or ProductNo ) exists in both tables, but they actually exists on both and then QlikView returns the same as if nothing has been done. What I need is something that checks if both tesults (2014 and A1) exists in their fields at the same row.

I have tied the following ways without result:

1- Use exists(Date) and exists(ProductNo) : QlikView also check separately the fields for the existence of a match.

2- Use exists( PrimaryKey) : QlikView checs for the existence of a field named PrimaryKey on the database, but it is concatenation of fields and does not exists as a separate field on it.

3- Use exists(Date & ProductNo) : same reason as 2.

4- Use exists(Date and ProductNo) : QlikView just crash and ask you to return tot he previous load data.

5- Use exists(Date) & exists(ProductNo) : desperate measure. This do not load the data properly and the field NSoldProd does not even appear.

I am aware that I can load the data with the PrimaryKey on a QVD and then reload and drop the previous table the new field using the option 2 (and that is what I have by the moment).

My questions are:

1- Is there a better way to do it without saving the data on a QVD ? May this way

2- Can the funcion exists() look if the values of two (or more) fields exist at the same row?

Thank You in advance i attach a QlikView document with the example used above for anyone who want to check it

Re: Multiple Field Exist (Cheking if values exist in different fields at the same row)

tresB — Fri, 18 Nov 2016 09:12:54 GMT

Try like:

Price:

LOAD Date,

ProductNo,

Date & ProductNo as PrimKey,

ProductPrice

FROM

[DB-Price.xlsx]

(ooxml, embedded labels, table is Sheet1);

Inner Keep

Sales:

LOAD Date & ProductNo as PrimKey,

NSoldProd

FROM

[DB-Sales.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Multiple Field Exist (Cheking if values exist in different fields at the same row)

swuehl — Fri, 18 Nov 2016 09:58:27 GMT

In addition, you may want to look into the two argument version of Exists()

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/InterRecordFunctions/Exists.htm‌

Price:

LOAD Date,

ProductNo,

Date & ProductNo as PrimKey,

ProductPrice

FROM

[DB-Price.xlsx]

(ooxml, embedded labels, table is Sheet1);

Sales:

LOAD Date & ProductNo as PrimKey,

NSoldProd

FROM

[DB-Sales.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE EXISTS(PrimKey,Date & ProductNo);

Test which approach performs better with your data and in your environment.

Re: Multiple Field Exist (Cheking if values exist in different fields at the same row)

Anonymous — Fri, 18 Nov 2016 11:06:59 GMT

Thank you for your answer, it worked so well but I am no sure about what is actually doing QlikView whe you use WHERE EXISTS(PrimKey,Date & ProductNo).

I guess that QlikView is searching for the fields Date and ProductNo, then join the fields via & and checks for the existence in memory for a field with the name PrimaryKey to mach this union.

Is it correct?

Re: Multiple Field Exist (Cheking if values exist in different fields at the same row)

Anonymous — Fri, 18 Nov 2016 11:11:16 GMT

Thank you very much for the reply, I think that you found the cleaner way to do it (I'll do it that way) but i have to mark the other answer with "Correct" because it deeps more in the understanding of the function exists.

Re: Multiple Field Exist (Cheking if values exist in different fields at the same row)

swuehl — Fri, 18 Nov 2016 11:20:17 GMT

For each record of your input table DB-Sales, QV will evaluate the expression

=Date & ProductNo

which will concatenate the two field values into a string.

Then this value will be looked up in the symbol table (distinct field values) of field PrimKey and checked if there is a match. Note that this symbol table updates with each new field value loaded in, so it's taking also the current LOAD and all previous LOADs into account.

If you are using the one argument version of Exists(), the working principle is the same, except that just the input field value is compared the field values loaded so far, no additional expression evaluation involved.

Re: Multiple Field Exist (Cheking if values exist in different fields at the same row)

Anonymous — Thu, 24 Nov 2016 09:07:51 GMT

Thank You very much for your answer, it was very usefull

Re: Multiple Field Exist (Cheking if values exist in different fields at the same row)

Marcos_Ferreira_dos_Santos — Wed, 04 Apr 2018 14:42:40 GMT

Thank you, guys! This helped me a lot today.

Re: Multiple Field Exist (Cheking if values exist in different fields at the same row)

abdul_mo — Thu, 13 Dec 2018 11:53:11 GMT

thanks a lot