Where Clause and Minimum Value

[peter789]

Hello,

I am really new to Qlik Sense and try to solve the following topic:

From the loaded Data in  COCKPIT I would like to show Data

(1) where VORGANGSSTATUS is NOT        DRUC, FREI, LÖKZ    OR     DRUC, FREI, RÜCK     OR    DRUC, FREI, TRÜC

(2) The Data in the field VORGANG is in the format 0010, 0020,... and i only want to show the smallest number. So propably convert the field VORGANG into integer and ONLY show the smallest number (minimum) for every AUFTRAG. 

Here's what I have so far...

Qualify*;
Unqualify;

COCKPIT:
LOAD
AUFTRAG,
VORGANG,
VORGANGSBEZEICHNUNG,
ARBEITSPLATZNR,
ARBEITSPLATZ,
SPAETESTES_ENDE,
IST_START,
IST_ENDE,
MENGE,
GUTMENGE,
Sum(MENGE) - Sum(GUTMENGE) as Offene_Menge,
VORGANGSSTATUS

FROM [lib://eins_QVD/FAUF_KAPA_RGP.qvd]
(qvd)
WHERE SPAETESTES_ENDE > 20201000

WHERE Not WildMatch(VORGANGSSTATUS,'*DRUC, FREI, LÖKZ*') and Not WildMatch(VORGANGSSTATUS,'*DRUC, FREI, RÜCK*') and Not WildMatch(VORGANGSSTATUS,'*DRUC, FREI, TRÜC*') 

GROUP By
AUFTRAG,
VORGANG,
VORGANGSBEZEICHNUNG,
ARBEITSPLATZNR,
ARBEITSPLATZ,
SPAETESTES_ENDE,
IST_START,
IST_ENDE,
MENGE,
GUTMENGE,
VORGANGSSTATUS
;

Please help!

[Anil_Babu_Samineni]

For both scenarios, Perhaps this

COCKPIT:
LOAD
AUFTRAG,
VORGANG,
VORGANGSBEZEICHNUNG,
ARBEITSPLATZNR,
ARBEITSPLATZ,
SPAETESTES_ENDE,
IST_START,
IST_ENDE,
MENGE,
GUTMENGE,
Sum(MENGE) - Sum(GUTMENGE) as Offene_Menge,
VORGANGSSTATUS
FROM [lib://eins_QVD/FAUF_KAPA_RGP.qvd] (qvd)
WHERE SPAETESTES_ENDE > 20201000 and (Not Match(VORGANGSSTATUS, 'DRUC', 'FREI', 'LÖKZ') or Not Match(VORGANGSSTATUS, 'DRUC', 'FREI', 'RÜCK') or Not Match(VORGANGSSTATUS, 'DRUC', 'FREI', 'TRÜC'))

Left Join (COCKPIT)
Load AUFTRAG, Min(Num(Num#(VORGANG))) as VORGANG Resident COCKPIT Group By AUFTRAG;

[peter789]

Hello,

thanks for your message.

So the first part works! I changed the part with Match to         WildMatch(VORGANGSSTATUS,'*FREI');

However the second part doesn't work. I try to explain it again: In the column of AUFTRAG right now there are several similar values, the values in VORGANG are changing. I only want to show in my future table the smallest value in VORGANG and its entry in AUFTRAG. I am sure with the picture I attached it's understandable.

So in my example in the picture: I want to have row 2 (AUFTRAG 1222 and VORGANG 0010) and row 5 (AUFTRAG 1334 and VORGANG 0030).

VORGANG is always as shown as a text and not a real number and needs to be transformed in a number i guess.

My script right now:

Qualify*;
Unqualify;

EINS:
LOAD
// RowNo() as ID,
AUFTRAG,
VORGANG,
VORGANGSBEZEICHNUNG,
ARBEITSPLATZNR,
ARBEITSPLATZ,
SPAETESTES_ENDE,
IST_START,
IST_ENDE,
MENGE,
GUTMENGE,
Sum(MENGE) - Sum(GUTMENGE) as Offene_Menge,
VORGANGSSTATUS

FROM [lib://hello/hello.qvd]
(qvd)

WHERE (SPAETESTES_ENDE > 20201000)
and WildMatch(VORGANGSSTATUS,'*FREI');

//Load *,
//AUFTRAG, Min(Num(Num#(VORGANG))) as VORGANG
//Resident EINS Group By AUFTRAG;

Please help! 🙂

[peter789]

To add one thing:

The values in VORGANG aren't always in descending order. So it need's to be found by min() i guess.