topic Re: if logic with group by in load script for field in QlikView

if logic with group by in load script for field

matthewp — Mon, 16 May 2016 10:38:31 GMT

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.

Re: if logic with group by in load script for field

sunny_talwar — Mon, 16 May 2016 10:44:06 GMT

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;

Re: if logic with group by in load script for field

matthewp — Mon, 16 May 2016 10:47:20 GMT

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

Re: if logic with group by in load script for field

Kushal_Chawda — Mon, 16 May 2016 10:49:20 GMT

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

Re: if logic with group by in load script for field

trdandamudi — Mon, 16 May 2016 10:51:42 GMT

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;

Re: if logic with group by in load script for field

sunny_talwar — Mon, 16 May 2016 10:55:08 GMT

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

Re: if logic with group by in load script for field

Kushal_Chawda — Mon, 16 May 2016 10:57:25 GMT

Do we really need group by here stalwar1‌ ?

Re: if logic with group by in load script for field

sunny_talwar — Mon, 16 May 2016 10:58:02 GMT

How will we do this without Group By?

Re: if logic with group by in load script for field

Kushal_Chawda — Mon, 16 May 2016 10:58:57 GMT

Can you try my if condition and check?

Re: if logic with group by in load script for field

sunny_talwar — Mon, 16 May 2016 11:00:31 GMT

Sure

Re: if logic with group by in load script for field

matthewp — Mon, 16 May 2016 11:00:52 GMT

Doesnt work. doesnt define Acheck by group only by the line

Re: if logic with group by in load script for field

matthewp — Mon, 16 May 2016 11:02:16 GMT

both give me an invalid expression error

Re: if logic with group by in load script for field

sunny_talwar — Mon, 16 May 2016 11:02:38 GMT

I got this

I see an extra MISSING for Luke

Re: if logic with group by in load script for field

matthewp — Mon, 16 May 2016 11:06:20 GMT

Getting an invalid expression error on reload

Re: if logic with group by in load script for field

sunny_talwar — Mon, 16 May 2016 11:08:13 GMT

When you are running the application i attached, it is giving error? Really? I just reloaded it and attached it. Can you share what error did you get?

Re: if logic with group by in load script for field

matthewp — Mon, 16 May 2016 11:10:36 GMT

Invalid expression

Left Join (resulttable)

LOAD IDField,

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

Resident resulttable

Where Part <> 'PALD'

Group By IDField;

Re: if logic with group by in load script for field

sunny_talwar — Mon, 16 May 2016 11:14:56 GMT

Would you be able to generate a logfile and share?

Re: if logic with group by in load script for field

Kushal_Chawda — Mon, 16 May 2016 11:37:27 GMT

try this

Table:

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,

Count(IDField) as CntID

count(if(Checknumber='NULL',IDField)) as CntNull,

Count(if(Part='PALD',IDField)) as CntPALD

Resident Table

Group By IDField;

Final:

LOAD *,

if((CntID=CntNull) or CntPALD>0,'Missing','Ok') as Acheck

Resident Table;

Drop table Table;

Drop fields CntID,CntNull,CntPALD;

Re: if logic with group by in load script for field

matthewp — Mon, 16 May 2016 11:43:11 GMT

this just makes everything missing

Re: if logic with group by in load script for field

sunny_talwar — Mon, 16 May 2016 11:49:33 GMT

Can you try re-running the attached application and send me the log file? I have already enabled the generation of the log file