6 Replies Latest reply: Nov 24, 2016 4:07 AM by Eric Vilarrasa Canadell RSS

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

    Eric Vilarrasa Canadell

      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