Set Analysis

[mario-sarkis]

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,

[sunny_talwar]

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

[mario-sarkis]

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 ?

[anbu1984]

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

[sunny_talwar]

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

];

[sunny_talwar · Accepted solution]

attaching a sample qvw for reference as well.

Best,

Sunny

[mario-sarkis]

oh thank you suindia

my mistake was didnt transform the bracket into Text.