topic Re: Grouping issue with file in QlikView

Grouping issue with file

Wed, 05 Aug 2015 13:22:06 GMT

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

Re: Grouping issue with file

datanibbler — Wed, 05 Aug 2015 13:25:48 GMT

Hi,

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

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

HTH

Re: Grouping issue with file

jonathandienst — Wed, 05 Aug 2015 13:33:19 GMT

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.

Re: Grouping issue with file

Wed, 05 Aug 2015 13:43:24 GMT

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

JestOBJNR	ProjectStatus	Status
PR00000002	E0001	@CLOSED
PR00000002	I0001	@CLOSED
PR00000002	I0002	@CLOSED
PR00000002	I0005	@CLOSED
PR00000002	I0028	@CLOSED
PR00000002	I0042	@CLOSED
PR00000002	I0045	@CLOSED
PR00000002	I0082	@CLOSED
PR00000002	I0295	@CLOSED
PR00000002	I0460	@CLOSED

Re: Grouping issue with file

jonathandienst — Wed, 05 Aug 2015 13:49:05 GMT

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);

Re: Grouping issue with file

sinanozdemir — Wed, 05 Aug 2015 14:01:57 GMT

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

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

Re: Grouping issue with file

Wed, 05 Aug 2015 14:03:43 GMT

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

Re: Grouping issue with file

jonathandienst — Wed, 05 Aug 2015 14:14:54 GMT

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

Re: Grouping issue with file

Wed, 05 Aug 2015 14:25:01 GMT

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

JestOBJNR	STAT
PR00000001	I0001
PR00000001	I0042
PR00000002	E0001
PR00000002	I0001
PR00000002	I0002
PR00000002	I0005
PR00000002	I0028
PR00000002	I0042
PR00000002	I0045
PR00000002	I0082
PR00000002	I0295
PR00000002	I0460
PR00000003	I0001
PR00000003	I0002
PR00000003	I0045
PR00000003	I0118
PR00000004	I0001
PR00000004	I0002
PR00000004	I0005
PR00000004	I0045
PR00000004	I0118
PR00000005	I0001
PR00000005	I0002
PR00000005	I0005
PR00000005	I0045
PR00000005	I0118
PR00000006	I0001
PR00000006	I0002
PR00000006	I0005
PR00000006	I0045
PR00000006	I0118
PR00000007	I0001
PR00000007	I0002
PR00000007	I0045
PR00000007	I0118
PR00000008	E0001
PR00000008	I0001
PR00000008	I0002
PR00000008	I0028
PR00000008	I0045
PR00000009	I0001
PR00000009	I0042
PR00000010	E0001
PR00000010	I0001
PR00000010	I0002
PR00000010	I0005
PR00000010	I0028
PR00000010	I0042
PR00000010	I0045
PR00000010	I0082
PR00000010	I0295
PR00000010	I0460
PR00000011	I0001
PR00000011	I0002
PR00000011	I0045
PR00000011	I0118
PR00000012	I0001
PR00000012	I0002
PR00000012	I0005
PR00000012	I0045
PR00000012	I0118
PR00000013	I0001
PR00000013	I0002
PR00000013	I0005
PR00000013	I0045
PR00000013	I0118
PR00000014	I0001
PR00000014	I0002
PR00000014	I0045
PR00000014	I0118
PR00000015	I0001
PR00000015	I0002
PR00000015	I0045
PR00000015	I0118
PR00000016	I0001
PR00000016	I0002
PR00000016	I0005
PR00000016	I0028
PR00000016	I0042
PR00000016	I0082
PR00000016	I0118
PR00000016	I0295
PR00000017	E0001
PR00000017	I0001
PR00000017	I0028
PR00000017	I0118
PR00000018	I0001
PR00000018	I0002
PR00000018	I0005
PR00000018	I0042
PR00000018	I0045
PR00000018	I0067
PR00000018	I0093
PR00000019	E0001
PR00000019	E0005
PR00000019	I0001
PR00000019	I0002
PR00000019	I0005
PR00000019	I0028
PR00000019	I0042
PR00000019	I0045
PR00000019	I0046
PR00000019	I0082
PR00000019	I0295
PR00000019	I0460

Re: Grouping issue with file

sasiparupudi1 — Wed, 05 Aug 2015 14:26:42 GMT

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;

Re: Grouping issue with file

jonathandienst — Wed, 05 Aug 2015 14:39:21 GMT

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 ));

Re: Grouping issue with file

jonathandienst — Wed, 05 Aug 2015 14:41:45 GMT

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!)

Re: Grouping issue with file

Wed, 05 Aug 2015 15:19:33 GMT

Thank you that worked