7 Replies Latest reply: Dec 18, 2013 8:57 AM by Henric Cronström RSS

    second join issue

    Michael Matern


      Hello,

       

      I try to create two new fields with left join within one table. The first join works, but as soon as I add the second join the script reload doesn't end. There is no error message. I don't understand why.

       

      left join VBEP

       

      load  ID,

               if(......) as XYZ

      resident

      VBEP

      ;

       

      left join VBEP

       

      load  ID,

               if(......) as ZYX

      resident

      VBEP

      ;

       

      Does somebody know why this happens?

       

      Best regards,

      Michael

        • Re: second join issue
          Michael Matern

          Any idea?

           

          Thanks!

            • Re: second join issue
              Stefan Wühl

              Should work (besides I would enclose the table name after the join in parentheses).

              I assume XYZ and ZYX are not already part of the resident table. How does your if() statements look in detail?

               

              As an alternative, you can look into using a mapping approach instead of the join.

                • Re: second join issue
                  Michael Matern

                  Hi, my if statements looks as follows:

                   

                  left join (VBEP)

                  load  %VBELN%UEPOS,

                          if(isnull(Billing_Date) and today() > Erstes_Datum,'unreliable',

                          if(isnull(Billing_Date) and today() <=Erstes_Datum,'not relevant',

                          if(Billing_Date = Erstes_Datum, 'reliable',

                          if(isnull(Prod_Order_LastChange_Reasonand Billing_Date > Erstes_Datum,'unreliable',

                          if (Prod_Order_LastChange_Reason > 1      and Billing_Date > Erstes_Datum,'reliable',

                          if (Prod_Order_LastChange_Reason = 1      and Billing_Date > Erstes_Datum,'unreliable',

                          if(isnull(Prod_Order_LastChange_Reasonand Billing_Date < Erstes_Datum,'reliable',

                          if (Prod_Order_LastChange_Reason > 1    and Billing_Date < Erstes_Datum,'reliable',

                        if(Prod_Order_LastChange_Reason = 1       and Billing_Date < Erstes_Datum,'unreliable', 'check'))))))))) as Pickup_Reliability_Type    

                  resident

                  VBEP

                  ;

                  left join (VBEP)

                  load  if(isnull(Billing_Date) and Prod_Order_Progress_Status = 80 and today() > Erstes_Datum, today() - Erstes_Datum,

                           if(Prod_Order_Progress_Status = 80 and Billing_Date > Erstes_Datum, Billing_Date - Erstes_Datum,

                           if(Prod_Order_Progress_Status <> 80, 'not relevant', 0))) as storage_time

                  resident

                  VBEP

                  ;

                   

                    • Re: second join issue
                      Henric Cronström

                      Use Preceding Load instead.

                       

                      HIC

                      • Re: second join issue
                        Henric Cronström

                        PS

                        Your second Join lacks a linking field, so it will create a cartesian product. That's why it takes time.

                          • Re: second join issue
                            Michael Matern

                            Hallo Henric,

                             

                            Thank you for your support! But would it be possible for you to explain it  using my case? Unfortunately I have difficulties to understand your article for "presceding load".

                             

                            Thank you in advance!

                             

                            BR

                            Michael

                              • Re: second join issue
                                Henric Cronström

                                Just put your additional Load in front of the Load statement that defines your VBEP table, i.e.

                                 

                                load  *,

                                        if(isnull(Billing_Date) and today() > Erstes_Datum,'unreliable',

                                        if(isnull(Billing_Date) and today() <=Erstes_Datum,'not relevant',

                                        if(Billing_Date = Erstes_Datum, 'reliable',

                                        if(isnull(Prod_Order_LastChange_Reasonand Billing_Date > Erstes_Datum,'unreliable',

                                        if (Prod_Order_LastChange_Reason > 1      and Billing_Date > Erstes_Datum,'reliable',

                                        if (Prod_Order_LastChange_Reason = 1      and Billing_Date > Erstes_Datum,'unreliable',

                                        if(isnull(Prod_Order_LastChange_Reasonand Billing_Date < Erstes_Datum,'reliable',

                                        if (Prod_Order_LastChange_Reason > 1    and Billing_Date < Erstes_Datum,'reliable',

                                      if(Prod_Order_LastChange_Reason = 1       and Billing_Date < Erstes_Datum,'unreliable', 'check'))))))))) as Pickup_Reliability_Type ;

                                Load ... FROM VBEP ... ;


                                Make sure that the Preceding Load has a star symbol and ends with a semicolon.


                                HIC

                      • Re: second join issue
                        christian juillard

                        Hi Michael,

                         

                        When the script never ends it can be because your joins give so many numerous records it takes a lot of time or (and  i would think it is) you are creating either a loop or numerous $syn fields on a lot of fields.

                         

                        SO to be sure, use the debugger and tick "10 records" to just evaluate your structure

                         

                        Best regards

                        Chris

                        • Re: second join issue
                          Henric Cronström

                          You should most likely not join in this situation.

                           

                          If you know that you want to keep the number of records in VBEP constant, then it is far better to use Applymap (See http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap).

                           

                          Or you can use a preceding Load:

                          Load *, if(......) as ZYX ;

                          Load *, if(......) as XYZ ;

                          Load ... From VBEP;

                          (See http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load)

                           

                          Or you can run several passes through VBEP:

                          tmpVBEP:

                          Load ... From VBEP;

                           

                          VBEP:

                          Load *, if(......) as XYZ Resident tmpVBEP;

                          Drop Table tmpVBEP;

                           

                          HIC