Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

maternmi
Contributor II

second join issue


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

1 Solution

Accepted Solutions

Re: second join issue

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

9 Replies
maternmi
Contributor II

Re: second join issue

Any idea?

Thanks!

Not applicable

Re: second join issue

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

MVP
MVP

Re: second join issue

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

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
Contributor II

Re: second join issue

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

Use Preceding Load instead.

HIC

Re: second join issue

PS

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

maternmi
Contributor II

Re: second join issue

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

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