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
but I am no sure about what is actually doing QlikView whe you use WHERE EXISTS(PrimKey,Date & ProductNo).
