Re: if logic with group by in load script for fiel... - Qlik Community

matthewp · ‎2016-05-16

I want to make a field called ACheck that will output either Missing or Ok.

However I'm not sure how to get the logic to apply to a whole group to return the correct data.

Sample Data:

IDField Description Checknumber Part

A1001 John ABC 656 A CALD

A1001 John ABC 756 D CALD

A1001 John ABC 856 T CALD

A1002 Mark NULL CALD

A1003 Luke ABC 482 A CALD

A1003 Luke NULL CALD

A1003 Luke ABC 912 T CALD

A1004 Matt NULL CALD

A1004 Matt ABC 777 H PALD

A1004 Matt NULL CALD

The logic needs to be as follows:

IF the Checknumber IS NULL for ALL IDField (NOT just NULL in some)

AND ignore where Part = PALD

Expected Output:

IDField Description Checknumber Part ACheck

A1001 John ABC 656 A CALD Ok

A1001 John ABC 756 D CALD Ok

A1001 John ABC 856 T CALD Ok

A1002 Mark NULL CALD Missing

A1003 Luke ABC 482 A CALD Ok

A1003 Luke NULL CALD Ok

A1003 Luke ABC 912 T CALD Ok

A1004 Matt NULL CALD Missing

A1004 Matt ABC 777 H PALD Missing

A1004 Matt NULL CALD Missing

As you can see even though the A1004 group aren't all NULL's they are still getting classed as missing because i want to ignore PALD.

sunny_talwar · ‎2016-05-16

Try this:

Table:

LOAD RowNo() as Key,

*;

LOAD * Inline [

IDField, Description, Checknumber, Part

A1001, John, ABC 656 A, CALD

A1001, John, ABC 756 D, CALD

A1001, John, ABC 856 T, CALD

A1002, Mark, NULL, CALD

A1003, Luke, ABC 482 A, CALD

A1003, Luke, NULL, CALD

A1003, Luke, ABC 912 T, CALD

A1004, Matt, NULL, CALD

A1004, Matt, ABC 777 H, PALD

A1004, Matt, NULL, CALD

];

Left Join (Table)

LOAD IDField,

If(Only(Checknumber) = 'NULL', 'Missing', 'OK') as ACheck

Resident Table

Where Part <> 'PALD'

Group By IDField;

matthewp · ‎2016-05-16

Can the Where Part <> 'PALD' be integrated into the if, purely because i dont want them removed from the load as they are used elsewhere in the document

Kushal_Chawda · ‎2016-05-16

Table:

LOAD *,

if(Checknumber='NULL' or Part='PALD','MISSING',

if(Checknumber<>'NULL','Ok')) ACheck

LOAD * Inline [

IDField, Description, Checknumber, Part

A1001, John, ABC 656 A, CALD

A1001, John, ABC 756 D, CALD

A1001, John, ABC 856 T, CALD

A1002, Mark, NULL, CALD

A1003, Luke, ABC 482 A, CALD

A1003, Luke, NULL, CALD

A1003, Luke, ABC 912 T, CALD

A1004, Matt, NULL, CALD

A1004, Matt, ABC 777 H, PALD

A1004, Matt, NULL, CALD

];

Update : ')' was missing in If

trdandamudi · ‎2016-05-16

See if the below works:

Replace below script:

Left Join (Table)

LOAD IDField,

If(Only(Checknumber) = 'NULL', 'Missing', 'OK') as ACheck

Resident Table

Where Part <> 'PALD'

Group By IDField;

Like below:

Left Join (Table)

LOAD IDField,

If(Only(Checknumber) = 'NULL' And Part <> 'PALD' , 'Missing', 'OK') as ACheck

Resident Table

Group By IDField;

sunny_talwar · ‎2016-05-16

Have you looked at the document? The rows with PALD are still available. They are not getting removed

Kushal_Chawda · ‎2016-05-16

Do we really need group by here stalwar1‌ ?

sunny_talwar · ‎2016-05-16

How will we do this without Group By?

Kushal_Chawda · ‎2016-05-16

Can you try my if condition and check?

sunny_talwar · ‎2016-05-16

Sure

if logic with group by in load script for field