topic Re: How to make a missing label selectable? in QlikView

How to make a missing label selectable?

mys-elf — Thu, 20 Sep 2018 08:19:44 GMT

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

Re: How to make a missing label selectable?

Peter_Cammaert — Thu, 20 Sep 2018 08:56:50 GMT

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

Re: How to make a missing label selectable?

Peter_Cammaert — Thu, 20 Sep 2018 09:00:14 GMT

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?

Re: How to make a missing label selectable?

mys-elf — Thu, 20 Sep 2018 09:11:06 GMT

That's right!

Re: How to make a missing label selectable?

mys-elf — Thu, 20 Sep 2018 09:12:43 GMT

About 1.5 million abnormal and 10.0 million normal rows.

Re: How to make a missing label selectable?

Peter_Cammaert — Thu, 20 Sep 2018 09:20:43 GMT

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.

Re: How to make a missing label selectable?

prieper — Thu, 20 Sep 2018 10:38:14 GMT

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

Re: How to make a missing label selectable?

mys-elf — Tue, 25 Sep 2018 08:26:12 GMT

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