Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
matthewp
Creator III
Creator III

if logic with group by in load script for field

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

A1002  Mark           NULL             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

A1002  Mark           NULL             CALD Missing

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.

22 Replies
sunny_talwar

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

A1002,  Mark,          NULL,            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
Creator III
Creator III
Author

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

Kush
MVP
MVP

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

A1002,  Mark,          NULL,            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
Master II
Master II

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

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

Capture.PNG

Kush
MVP
MVP

Do we really need group by here stalwar1‌ ?

sunny_talwar

How will we do this without Group By?

Kush
MVP
MVP

Can you try my if condition and check?

sunny_talwar

Sure