topic Re: Set Analysis in QlikView

Set Analysis

mario-sarkis — Thu, 04 Jun 2015 14:19:17 GMT

hey all

if i want to compare two filed and these two fields are brackets not a single ammount for example

brack1 brack2

[100-200] >[50-100]

if i can can i know how to write it in a set analysis ?

example if(brack1>brack2,red(),Grey()).

a seconde question is can i compare a single amount to a bracket example:

amount brackt

300 > [100-200]

my condition is if(amount>brackt,Red(),Grey())

Thank you,

Re: Set Analysis

sunny_talwar — Thu, 04 Jun 2015 14:30:02 GMT

I would use a dual function to create them in the script, may be something like this:

LOAD allYourFields,

Dual(brack1, (Num#(TextBetween(brack1, '[', '-'), '##') + Num#(TextBetween(brack1, '-', ']'), '##'))/2) as brack1,

Dual(brack2, (Num#(TextBetween(brack2, '[', '-'), '##') + Num#(TextBetween(brack2, '-', ']'), '##'))/2) as brack2

FROM ...

This will give brack1 a numeric value (for example, [100-200] will have a numeric value of 150 and [50-100] will have a numeric value of 75). Then you can use it to compare the two strings anywhere as they have numeric values attached to them.

HTH

Best,

Sunny

Re: Set Analysis

mario-sarkis — Fri, 05 Jun 2015 08:06:21 GMT

this is how my bracket look like :

>8000,000
01-25,000
25,001-75,000
75,001-100,000
100,001-200,000
500,001-1000,000
600,001-8000,000
1000,001-2000,000
2000,001-4000,000
4000,001-6000,000

i loaded All my fields in the script

but this gave me an empty Field>>>> Dual(brack1, (Num#(TextBetween(brack1, '[', '-'), '##') + Num#(TextBetween(brack1, '-', ']'), '##'))/2) as brack1,

can you help ?

Re: Set Analysis

anbu1984 — Fri, 05 Jun 2015 09:19:46 GMT

Don't you have square bracket in your input?

If you don't have overlapping range, then try this

if(Subfield(brack1,'-',-1) >Subfield(brack2,'-',-1),red(),Grey()).

For the second question you can try like this,

if(amount>Subfield(brackt,'-',-1),Red(),Grey())

Re: Set Analysis

sunny_talwar — Fri, 05 Jun 2015 09:26:59 GMT

Try this in that case:

Table:

LOAD Text(brack1) as brack1,

Dual(brack1, If(WildMatch(brack1, '>*'), Num#(PurgeChar(brack1, '>'), '##'),

(Num#(SubField(PurgeChar(brack1, '>'), '-', 1), '##') + Num#(SubField(PurgeChar(brack1, '>'), '-', 2), '##'))/2)) as brack2;

LOAD * Inline [

brack1

>8000000

01 - 25000

25001 - 75000

75001 - 100000

100001 - 200000

500001 - 1000000

600001 - 8000000

1000001 - 2000000

2000001 - 4000000

4000001 - 6000000

];

Re: Set Analysis

sunny_talwar — Fri, 05 Jun 2015 09:33:36 GMT

attaching a sample qvw for reference as well.

Best,

Sunny

Re: Set Analysis

mario-sarkis — Fri, 05 Jun 2015 09:41:13 GMT

oh thank you suindia

my mistake was didnt transform the bracket into Text.