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
datanibbler
Champion
Champion

Hi,

then just modify your LOAD with a new calculated field, like

>> LOAD OBJNR, STAT, IF(STAT='I0046','OPEN', '@CLOSED') as Status ... <<

HTH

jonathandienst
Partner - Champion III
Partner - Champion III

Are those the only codes involved?

JESTPR:

LOAD

OBJNR As JestOBJNR,

STAT As ProjectStatus,

If(STAT = 'I0046', 'Open', 'Closed') As OpenState

FROM

(qvd);

If there are more codes to interpret, you might want to use a mapping table.

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

Thanks

But the issue is that there are many different codes that will define CLOSED status and I want to group them together to display CLOSED against one OBJNR

e,g

So I need to group all of the projectStatus codes to show 1 record of CLOSED against OBJNR PR0000002

JestOBJNRProjectStatusStatus
PR00000002E0001@CLOSED
PR00000002I0001@CLOSED
PR00000002I0002@CLOSED
PR00000002I0005@CLOSED
PR00000002I0028@CLOSED
PR00000002I0042@CLOSED
PR00000002I0045@CLOSED
PR00000002I0082@CLOSED
PR00000002I0295@CLOSED
PR00000002I0460@CLOSED
jonathandienst
Partner - Champion III
Partner - Champion III

In the front end, create a table with JestOBJNR and Status as dimensions, and wherever metrics you require in the expressions. The two dimension values will only be displayed once.

If there is no other data in your source table, then do this:

JESTPR:

LOAD DISTINCT

OBJNR As JestOBJNR,

If(STAT = 'I0046', 'Open', 'Closed') As OpenState

FROM

(qvd);

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sinanozdemir
Specialist III
Specialist III

It seems like you want to create per record per OBJNR per status:

Capture.PNG

If so, you can accomplish this either in the front end or in the load script with another preceding load statement:

Capture.PNG

Not applicable
Author

Hi

No that doesn't work

I need to end up with an open or closed status against each OBJNR

if STAT = 'I0046' then the record should show Closed

However if the record isnt 'I0046' there can be many other codes that represent OPEN status and I need to group these together against 1 OBJNR


jonathandienst
Partner - Champion III
Partner - Champion III

Well that is exactly what that script does -  the DISTINCT will collapse all the Closed lines into a single line.

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

Yes understand but see the example below.

I want to be able to show 1 JestOBJNR record that has an open or closed status

Closed status will always be 'I0046' but there will be other status recorded against the OBJNR

if 'I0046@ isnt present then the status is closed but I only want one record

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


JestOBJNRSTAT
PR00000001I0001
PR00000001I0042
PR00000002E0001
PR00000002I0001
PR00000002I0002
PR00000002I0005
PR00000002I0028
PR00000002I0042
PR00000002I0045
PR00000002I0082
PR00000002I0295
PR00000002I0460
PR00000003I0001
PR00000003I0002
PR00000003I0045
PR00000003I0118
PR00000004I0001
PR00000004I0002
PR00000004I0005
PR00000004I0045
PR00000004I0118
PR00000005I0001
PR00000005I0002
PR00000005I0005
PR00000005I0045
PR00000005I0118
PR00000006I0001
PR00000006I0002
PR00000006I0005
PR00000006I0045
PR00000006I0118
PR00000007I0001
PR00000007I0002
PR00000007I0045
PR00000007I0118
PR00000008E0001
PR00000008I0001
PR00000008I0002
PR00000008I0028
PR00000008I0045
PR00000009I0001
PR00000009I0042
PR00000010E0001
PR00000010I0001
PR00000010I0002
PR00000010I0005
PR00000010I0028
PR00000010I0042
PR00000010I0045
PR00000010I0082
PR00000010I0295
PR00000010I0460
PR00000011I0001
PR00000011I0002
PR00000011I0045
PR00000011I0118
PR00000012I0001
PR00000012I0002
PR00000012I0005
PR00000012I0045
PR00000012I0118
PR00000013I0001
PR00000013I0002
PR00000013I0005
PR00000013I0045
PR00000013I0118
PR00000014I0001
PR00000014I0002
PR00000014I0045
PR00000014I0118
PR00000015I0001
PR00000015I0002
PR00000015I0045
PR00000015I0118
PR00000016I0001
PR00000016I0002
PR00000016I0005
PR00000016I0028
PR00000016I0042
PR00000016I0082
PR00000016I0118
PR00000016I0295
PR00000017E0001
PR00000017I0001
PR00000017I0028
PR00000017I0118
PR00000018I0001
PR00000018I0002
PR00000018I0005
PR00000018I0042
PR00000018I0045
PR00000018I0067
PR00000018I0093
PR00000019E0001
PR00000019E0005
PR00000019I0001
PR00000019I0002
PR00000019I0005
PR00000019I0028
PR00000019I0042
PR00000019I0045
PR00000019I0046
PR00000019I0082
PR00000019I0295
PR00000019I0460
sasiparupudi1
Master III
Master III

something like this?

JESTPR:

LOAD

OBJNR As JestOBJNR,

STAT As ProjectStatus,

If(STAT = 'I0046', 'Open', 'Closed') As OpenState

FROM

(qvd);

NoConcatenate

JESTPR1:

LOAD OBJNR As JestOBJNR,Concat(ProjectStatus,',') as TotalProjectStatus,OpenState

Resident JESTPR

group by JestOBJNR,OpenState;

drop table JESTPR;