second join issue

[maternmi]

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

[maternmi]

Any idea?

Thanks!

[#3]

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

[swuehl]

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.

[hic]

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

[maternmi]

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_Reason)  and 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_Reason)  and 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

;

[hic]

Use Preceding Load instead.

HIC

[hic]

PS

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

[maternmi]

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

[hic · Accepted solution]

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_Reason)  and 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_Reason)  and 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