Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Grouping issue with file


Hi

I'm trying to group some records together. I have the following

 

JESTPR:

LOAD
OBJNR As JestOBJNR,
STAT As ProjectStatus

FROM

(
qvd)

The field STAT has multiple records to each record in OBJNR

STAT has a record called 'I0046' and if the record is equal to this then I want to call it 'OPEN' return it with the field OBJNR

If the field is not equal to 'I0046' then I want to group together the records and call them @CLOSED and return it with the field OBJNR

12 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Andrew Thomas wrote:

THe DISTINCT logic worked to the point where it gave me 1 record for open and 1 for closed against the same OBJNR

That's correct. You did not specify that you want suppress the open if there are any closes...

This will prevent loading open if any close states exist.

JESTPR:

LOAD DISTINCT

OBJNR As JestOBJNR,

'Closed' As OpenState

FROM (qvd)

WHERE STAT <> 'I0046';

Concatenate

LOAD DISTINCT

OBJNR As JestOBJNR,

'Open' As OpenState

FROM (qvd)

WHERE STAT = 'I0046' AND Not(Exists(JESTPR));

WHERE STAT = 'I0046' AND Not(Exists(OBJNR ));

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

JESTPR:

LOAD DISTINCT

OBJNR As JestOBJNR,

'Closed' As OpenState

FROM (qvd)

WHERE STAT <> 'I0046';

Concatenate

LOAD DISTINCT

OBJNR As JestOBJNR,

'Open' As OpenState

FROM (qvd)

WHERE STAT = 'I0046' AND Not(Exists(JestOBJNR, OBJNR));

(Finally got that right!)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thank you that worked