topic Re: second join issue in QlikView

second join issue

maternmi — Wed, 18 Dec 2013 09:33:26 GMT

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

maternmi — Wed, 18 Dec 2013 10:28:18 GMT

Any idea?

Thanks!

Re: second join issue

Wed, 18 Dec 2013 10:33:41 GMT

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

swuehl — Wed, 18 Dec 2013 10:37:59 GMT

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

hic — Wed, 18 Dec 2013 10:44:05 GMT

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

Re: second join issue

maternmi — Wed, 18 Dec 2013 10:53:43 GMT

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

;

Re: second join issue

hic — Wed, 18 Dec 2013 12:13:48 GMT

Use Preceding Load instead.

HIC

Re: second join issue

hic — Wed, 18 Dec 2013 12:15:36 GMT

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

Re: second join issue

maternmi — Wed, 18 Dec 2013 13:11:47 GMT

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!

Michael

Re: second join issue

hic — Wed, 18 Dec 2013 13:57:08 GMT

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