topic Re: Qualify breaks my script in QlikView

Qualify breaks my script

max_potass — Tue, 13 Mar 2018 16:16:06 GMT

Hi everyone,

I have a Problem with my Script:

As soon as I put qualify and unqualify before, it just stops the import at the last tablefile without an errror. It appears to be doing something, but it doesn't for like 10 minutes. After that I decided, that something is wrong, as the import itself takes only about 2 Minutes.

Qualify *;

UNQUALIFY MLC_key;

UNQUALIFY Plan_key;

Ist:

Load

[Material]&[Verkaufsorganisation]&[stat. Land]&[MMM.YY] as MLC_key,

[Leading Product]&[Verkaufsorganisation]&[stat. Land]&[MMM.YY] as Plan_key,

True_Date,

Prev_Date,

Year,

[MMM.YY],

[Verkaufsorganisation],

[Verkaufsorganisation TEXT],

Text([stat. Land]) as [stat. Land],

[Material],

[Material TEXT],

[Markteinführungstermin],

[Neuprodukt],

[Rep_HaWa_Normal]

[Leading Product_PMM]

[Leading Product]

From \\qlikpv01\BusinessUnit\Ist_Temp.qvd (qvd);

Left Join LOAD

Text((LP)) as [Leading Product_PMM],

Text((LPC)) as [Leading Product Cluster TEXT_PMM],

BU as BU_PMM

FROM

\\qlikpv01\businessunit\LP_LPC_BU.xlsx

(ooxml, embedded labels, table is Tabelle1);

Left Join LOAD

Text((LP)) as [Leading Product],

Text((LPC)) as [Leading Product Cluster TEXT],

BU as BU

FROM

\\qlikpv01\businessunit\LP_LPC_BU.xlsx

(ooxml, embedded labels, table is clean);

Can someone Help? This works perfectly fine, if I delete Qualify and Unqualify

Re: Qualify breaks my script

Peter_Cammaert — Tue, 13 Mar 2018 16:24:01 GMT

Just curious: on which common fields are you performing those JOINs? Or is it your intention to perform two cartesian products?

Re: Qualify breaks my script

max_potass — Wed, 14 Mar 2018 08:11:12 GMT

Hi Peter,

Sorry, I acutally deleted the joined fields, as there are like 15 more and I did not wanted to overload this post. I fixed it in the original post.

This script works absolutly fine, if I delete Qualify and Unqualify

Re: Qualify breaks my script

Peter_Cammaert — Wed, 14 Mar 2018 08:58:31 GMT

Well it helps if you post a representative script. The entire script is often best (if it's too extensive, add it as an attachment) because root causes for problems may be hidden in parts that you may think are not relevant.

With respect to the Qualify problem, I devised this custom script for you to discover what happens when you try to JOIN qualified tables. Check it out:

QUALIFY *;
UNQUALIFY MLC_key, Plan_key;


Ist:
LOAD Mat & SalesOrg & MY AS MLC_key,
     If (Len(trim(LeadProd)) = 0, '999', LeadProd) & SalesOrg & MY AS Plan_key,
     Mat,
     SalesOrg,
     JoinField1,
     JoinField2
INLINE [
MY, Mat, SalesOrg, LeadProd, JoinField1, JoinField2
201701, ABC, S1,, A, 1
201801, DEF, S2, 123, B, 2
]; 


LEFT JOIN
LOAD ID1 AS JoinField1,
     Txt1 AS JoinField1Text
INLINE [
ID1, Txt1
A, TextA
B, TextB
];


LEFT JOIN
LOAD ID2 AS JoinField2,
     Txt2 AS JoinField2Text
INLINE [
ID2, Txt2
1, NumberOne
2, NumberTwo
];

Reload and have a look at how the JOINS get inserted into your table (and cause a couple of cartesian products). Indeed, when using QUALIFY, every table gets qualified right after it has been loaded.

You'ld better create the table first with JOINS and all, and then qualify-load it into another.

Best,

Peter

Re: Qualify breaks my script

max_potass — Wed, 14 Mar 2018 09:29:20 GMT

Hello Peter,

thank you for your answer. I don't get, why it says INLFED.* . This is not what the QlikView Manual says. But anyway this is what I wanted, but it is not working.

But acutally I found an easier way to achieve what I want, so I will not use Qualify.

Best regards

Max

Re: Qualify breaks my script

Peter_Cammaert — Wed, 14 Mar 2018 09:55:00 GMT

I don't remember the QlikView manual saying anything about the default name an INLINE LOAD gets when it doesn't get one from you. The INLFED is assigned by the script engine because no name was specified for that table, and the QUALIFY forces it to have one in order to use as a prefix for the containing fields.

Combining QUALIFY and JOINs creates internal conflicts that cannot be resolved. So you should avoid using them.

Please close the discussion by marking Correct/Helpful any reply you feel has contributed towards resolving your issue. That way other Community members will know that you issue has been handled and doesn't need any further feedback. Thanks.

Re: Qualify breaks my script

max_potass — Wed, 14 Mar 2018 10:44:52 GMT

Hello Peter,

if I choose the table IST in my application I get the joined fields, so I assumed the joined tables are also qualified as IST. But anyway. Does not really matter.

I will do. Thanks.