Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
ericarru
Contributor

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

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:


DateProductNo=Sum(NSoldProd)
201401A110
201401A215
201402A213
201403A130
201403A218
--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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

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/E...

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.

8 Replies
MVP
MVP

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

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);

Highlighted
MVP
MVP

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

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/E...

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.

ericarru
Contributor

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

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?

ericarru
Contributor

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

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.

MVP
MVP

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

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.

ericarru
Contributor

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

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

Marcos_2012
New Contributor III

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

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

abdul_mo
New Contributor II

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

thanks a lot

Community Browser