Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
qvmyself
New Contributor

How to make a missing label selectable?

Hi everyone,

I am currently failing to solve the following problem:

I have a large statistics table with a plant.ID and a materials.ID as the unique key. In addition, I have a table where certain plant-material combinations are defined as exceptions with an exception text. Now I also want to make the combinations of my application selectable that are not exceptions and mark them as "normal".

As a way out I thought something like that, but there is at least one error somewhere. Maybe it's too complicated and there's a much easier way to solve it:

TMP.MATERIAL.ART.1:

LOAD PLANT_MAT.ID as FOC.PLANT_MAT.ID,

//     MATERIALS.ID,

//     PLANT.ID,

     FOCUS.DESCRIPTION as MATERIALS.ART

FROM

(ooxml, embedded labels, table is [ABC-FOKUSPRODUKTE]);

TMP.MATERIAL.ART.2:

LOAD PLANT.ID&'.'&MATERIALS.ID as FOC.PLANT_MAT.ID

FROM

(qvd)WHERE(CUSTOMER.ID <> '123450000') and (PLANT.ID >= '1234' and PLANT.ID <= '4321');

NoConcatenate

MATERIAL.ART:

LOAD FOC.PLANT_MAT.ID,

//     MATERIALS.ID,

//     PLANT.ID,

     MATERIALS.ART

Resident TMP.MATERIAL.ART.1;

Concatenate(MATERIAL.ART)

LOAD FOC.PLANT_MAT.ID,

'Normal' as MATERIALS.ART

Resident TMP.MATERIAL.ART.2

WHERE Not Exists(FOC.PLANT_MAT.ID);

DROP Tables TMP.MATERIAL.ART.1,TMP.MATERIAL.ART.2;

A LookUp or an ApplyMap were simply too slow with the amount of data.

Many thanks in advance,

Matthias

Tags (2)
7 Replies

Re: How to make a missing label selectable?

What is not working? You don't get any Material/Plant combinations tagged as 'Normal'?

Re: How to make a missing label selectable?

BTW ApplyMap() is an extremely fast lookup function and often beats JOINs and copies. I really can't imagine that any other solution would be faster. What amount of data are we talking about; how many 'Normal' rows, and how many 'Abnormal' ones?

qvmyself
New Contributor

Re: How to make a missing label selectable?

That's right!

qvmyself
New Contributor

Re: How to make a missing label selectable?

About 1.5 million abnormal and 10.0 million normal rows.

Re: How to make a missing label selectable?

As far as I can see, the most probably cause is the content of field FOC.PLANT_MAT.ID. You see, during script execution, a field (through its symbol table) combines all values present in all columns with the same name in all tables that were loaded previously. And the combination of FOC.PLANT_MAT.ID values probably spans all values, both Normal and "Abnormal". The result is that the WHERE NOT EXISTS() in the last CONCATENATE LOAD doesn't really work.

You can solve this by creating a temporary field in the NOCONCATENATE LOAD, like

MATERIAL.ART:

NOCONCATENATE

LOAD FOC.PLANT_MAT.ID,

     FOC.PLANT_MAT.ID AS TEMP.FOC.PLANT_MAT.ID,

     MATERIALS.ART

RESIDENT TMP.MATERIAL.ART.1;

and now checking against this temporary field in the WHERE NOT EXISTS expression.

prieper
Honored Contributor II

Re: How to make a missing label selectable?

What about keeping the Exceptions separately?

aircode:

SET OtherSymbol = +;

Production:

.....

Exceptions:

LOAD Key, ..., 'Y' AS IsException FROM .....

CONCATENATE (Exception) LOAD '+'  AS Key, 'N' AS IsException AUTOGENERATE 1;

Peter

qvmyself
New Contributor

Re: How to make a missing label selectable?

Thank you very much, gentlemen. I was able to work out a solution from both answers.