Discussion Board for collaboration on QlikView Scripting.
I'm trying to group some records together. I have the following
LOAD OBJNR As JestOBJNR,STAT As ProjectStatusFROM(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
then just modify your LOAD with a new calculated field, like
>> LOAD OBJNR, STAT, IF(STAT='I0046','OPEN', '@CLOSED') as Status ... <<
Are those the only codes involved?
OBJNR As JestOBJNR,
STAT As ProjectStatus,
If(STAT = 'I0046', 'Open', 'Closed') As OpenState
If there are more codes to interpret, you might want to use a mapping table.
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
So I need to group all of the projectStatus codes to show 1 record of CLOSED against OBJNR PR0000002
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:
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:
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
Well that is exactly what that script does - the DISTINCT will collapse all the Closed lines into a single line.
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
something like this?
LOAD OBJNR As JestOBJNR,Concat(ProjectStatus,',') as TotalProjectStatus,OpenState
group by JestOBJNR,OpenState;
drop table JESTPR;