topic Re: string function "match", right trim or crosstable? in QlikView

string function "match", right trim or crosstable?

josephinetedesc — Tue, 27 Dec 2016 03:45:04 GMT

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

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

Re: string function "match", right trim or crosstable?

el_aprendiz111 — Tue, 27 Dec 2016 13:54:47 GMT

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;

Re: string function "match", right trim or crosstable?

MarcoWedel — Wed, 28 Dec 2016 00:37:41 GMT

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

Re: string function "match", right trim or crosstable?

josephinetedesc — Thu, 29 Dec 2016 10:16:58 GMT

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.

Re: string function "match", right trim or crosstable?

josephinetedesc — Thu, 29 Dec 2016 12:14:50 GMT

Hi Marco

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

Re: string function "match", right trim or crosstable?

josephinetedesc — Thu, 05 Jan 2017 22:44:22 GMT

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

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

Re: string function "match", right trim or crosstable?

MarcoWedel — Thu, 05 Jan 2017 23:00:10 GMT

Qlik Community Tip: Marking Replies as Correct or Helpful

hope this helps

regards

Marco