string function "match", right trim or crosstable?

[josephinetedesc]

Hi All

what is the best way to tackle this? 

The column headings are:

ID     XXXDir     YYYYDir     ZZZZZDir     XXXIndir     YYYYIndir     ZZZZZIndir

a     200                 20                0                      300           0                   50

b     0                       9                10                     100           12                 15

I would like to categorise the columns thus.

so the Department is one of XXX or YYYY or ZZZZZ,

and it is further categorised into Dir and Indir

I have a feeling it is going to be many crosstables ... and if statements ...

Thank you

Jo

Could I  get away with just having list boxes as the departments?  Would this make it simpler?

[el_aprendiz111]

Hi,

A possible solution

CrossTable(Department , Data)
tmp:
LOAD * Inline
[
ID,XXXDir,YYYYDir,ZZZZZDir,XXXIndir,YYYYIndir,ZZZZZIndir
a,200,20,0,300,0,50
b,0,9,10,100,12,15
];
NoConcatenate

SUMARY:
LOAD *,
SubField(SubField(Department ,'I',1),'D',1) AS Department2,
MID(Department, index(Department,'I')) & MID(Department, index(Department,'D')) as Category

Resident tmp;

DROP Table tmp;

[MarcoWedel]

Hi,

an attempt on a generic solution using a mapping table to identify category names could be:

mapCategory:
Mapping
LOAD Cat,
    '@start@'&Cat&'@end@'
Inline [
    Cat
    Dir
    Indir,
    AnotherDir
];

tabDepTemp:
CrossTable (DepCat, Fact)
LOAD * INLINE [
    ID, XXXDir, YYYYDir, ZZZZZDir, XXXIndir, YYYYIndir, ZZZZZIndir, XXXAnotherDir, YYYYAnotherDir, ZZZZZAnotherDir
    a, 200, 20, 0, 300, 0, 50, 10, 20, 30
    b, 0, 9, 10, 100, 12, 1, 40, 50, 60
];

tabDepartments:
LOAD *,
    RecNo() as RecNo,
    TextBetween(MapSubString('mapCategory',Right(DepCat,IterNo())),'@start@','@end@') as Category,
    SubField(MapSubString('mapCategory',Right(DepCat,IterNo())),'@start@',1) as Department,
    IterNo() as Length
Resident tabDepTemp
While IterNo() <= Len(DepCat);
Right Join
LOAD RecNo,
    Max(Length) as Length
Resident tabDepartments
Where Len(Department)
Group By RecNo;

DROP Table tabDepTemp;

hope this helps

regards

Marco

[josephinetedesc]

Thank you Fer - I thought I would start with your solution first:

I have a couple of departments which pose a problem:

bbIbbDir and bbIbbIndir this give the department name of bb??

ICCDir and ICCIndir gives a blank department name

ImmDir and ImmIndir give a blank department name

I imagine this is because of using "I"  and "D" as a delimiter.

I am going to take this in "small steps" and see if I can fix this ...

SubField(SubField(Department ,'I',1),'D',1) AS Department2,

I fixed this by:

SubField(SubField(Department ,'In',1),'Di',1) AS Department2,

MID(Department, index(Department,'In')) & MID(Department, index(Department,'Di')) as Category

I will need to look up subfields and MID to see why it worked.

Jo

[josephinetedesc]

Hi Marco

I didn't see the actual solution so I will not be able to look at it until tomorrow.  Will check!

Jo

[josephinetedesc]

Hi Marcus

I have been looking at the generic solution, but  ... my programming skills are not up to it.  I have saved it though and will look at it again.

Thank you

Jo

ps not sure how to change the status to answered - would you be able to do that for me?

[MarcoWedel]

Qlik Community Tip: Marking Replies as Correct or Helpful

hope this helps

regards

Marco