3 Replies Latest reply: Aug 2, 2017 8:04 AM by Court van de Lisdonk RSS

    Compare two fields from two tables without direct relations

    Court van de Lisdonk

      Hello

       

      I have two tables, without a direct relation between them, so no related or equal fields

      The first table is an IntervalTable with next content:

      PK

      Sequence

      Code

      Description

      MinimumIncludedCalendarDays

      MaximumIncludedCalendarDays

       

      1

      10

      -14

      < -14 Tage

      -999999999999

      -15

       

      2

      20

      -8

      -14<=Tage<=-8

      -14

      -8

       

      3

      30

      -2

      -7<=Tage<=-2

      -7

      -2

       

      4

      40

      1

      -1<=Tage<=1

      -1

      1

       

      5

      50

      2

      2<=Tage<=7

      2

       

      7

      6

      60

      8

      8<=Tage<=14

      8

       

      14

      7

      70

      14

      > +14 Tage

      15

      9.999.999.99

      8

      90

      0

      Keine Tage

      0

      0

       

      In my second table I calculate the amounts of days between three dates Postingdate, ShippingDateRequested and ShippingDateConfirmed:

      Temp_SalesInvLineShippingDates: LOAD
      InvoiceNumber,
      Date(PostingDate)                                                 as PostingDate,
      Date(ShippingDateRequested)                                as ShippingDateRequested,
      Date(ShippingDateConfirmed)                                as ShippingDateConfirmed,
      /* create interval days */
      (PostingDate - ShippingDateRequested)                  as DaysRequested,
      (PostingDate - ShippingDateConfirmed)                  as DaysConfirmed;

      SQL SELECT *
      FROM SalesInvoice.SalesInvLineShippingDates;

       

      Now I want add one code for Requested (CodeRequested) and one code for Confirmed (CodeConfirmed) into my TempTable both equal the Code from the first table (=IntervalTable) when DaysRequested and DaysConfirmed are between MinimumIncludedCalendarDays and MaximumIncludedCalendarDays.

       

      Is there a command for my TempTable like:

       

      If (IntervalTable. MinimumIncludedCalendarDays <= Days DaysRequested <= IntervalTable. MaximumIncludedCalendarDays, Code, 0) as  CodeRequested

       

      If (IntervalTable. MinimumIncludedCalendarDays <= Days DaysConfirmed <= IntervalTable. MaximumIncludedCalendarDays, Code, 0) as  CodeConfirmed

       

       

      I watched and searched for solutions on the site but I didn’t found any solution.  I read LOAD DATA INTO QLIKVIEW, but there was not a solution for my problem .

       

      Is there a solution within the script? Or should I solve this problem with Set Analysis?

       

      Thanks in advance

      Regards

       

      Court