Extend variable classes

[cscherer]

Hello,

I want to analyse my data with different intervals. The intervals are not even, so perhaps the first interval is from 0 to9, the next one is from 11 to 20 and so on.

I found this script in the community:

FactTable:

LOAD city,

     amount

FROM

(ooxml, embedded labels, table is Tabelle1);

TempBuckets:

LOAD * INLINE [

START, END ,bucket

0, 5, '0-5'

6, 10, '6-10'

11, 15, '11-15'

15, 20, '16-20'

];

TempBridge:

IntervalMatch(amount)

Load START, END Resident TempBuckets;

Left Join(TempBridge)

Load * resident TempBuckets;

DROP Table TempBuckets;

Left Join(FactTable)

Load

     amount,

     bucket

Resident

     TempBridge

;

DROP Table TempBridge;

Thanks for that.

But how can I add another "TempBuckets"? I want to choose between "TempBucket 1" with the buckets from 0-5, 6-10, ... and between "TempBucket2" with the classes from 0-9, 10-20, ...

Is that possible?

Thanks in advance for your help!

Best regards,

Clemens

[marcus_sommer]

I think you could repeat your logic with a second intervalmatch + joining for a second cluster. An alternatively (if there are not too much intervals you could create such tables in excel in a few minutes) to your approach might be to use mapping-tables or simply associate these tables within the datamodel.

- Marcus

[vinieme12]

**UPDATED*********

The below will work if you have a standard class of 5

For your script:

FactTable:

LOAD city,

     amount

floor(amount,5,5)+if(mod(amount,5)=0,-5,0)+1 &'-'& (ceil(amount,5,5)) as bucket

FROM

(ooxml, embedded labels, table is Tabelle1);

My Test Script

TEMP:

LOAD * ,floor(Price,5,5)+if(mod(Price,5)=0,-5,0)+1 &'-'& (ceil(Price,5,5)) as bucket INLINE [

Category,Price

A,1

B,2

C,11

x,9

y,12

z,15

l,22

m,39

n,40

r,41

g,45

b,49

i,50

o,51

u,60

];

[vinieme12]

let me know if it worked for u

[cscherer]

Thank you for your answer. I`ve tried to build the code, but how do I have to go on? How can I choose between the two buckets?

btw, the second bucket didn`t join to the fact table. What`s wrong there?

Thanks in advance.

FactTable:

LOAD city,

     amount

FROM

(ooxml, embedded labels, table is Tabelle1);

TempBuckets:

LOAD * INLINE [

START, END , bucket

0, 9, '0-9'

10, 20, '10-20'

];

TempBridge:

IntervalMatch(amount)

Load START, END Resident TempBuckets;

Left Join(TempBridge)

Load * resident TempBuckets;

DROP Table TempBuckets;

TempBuckets2:

LOAD * INLINE [

START, END , bucket2

0, 5, '0-5'   

6, 11, '6-11'

12, 18, '12-18'

19, 20, '19-20'

];

TempBridge:

IntervalMatch(amount)

Load START, END Resident TempBuckets2;

Left Join(TempBridge)

Load * resident TempBuckets2;

DROP Table TempBuckets2;

Left Join(FactTable)

Load

     amount,

     bucket,

     bucket2

Resident

     TempBridge;

DROP Table TempBridge;

[cscherer]

Thanks for your answer, but my classes have different widths and not always 5.

Regards

[marcus_sommer · Accepted solution]

This will work:

FactTable:

LOAD * inline [

city, amount

a, 1

b, 3

c, 5

d, 7

e, 9

f, 11

g, 13

h, 15

];

//FROM (ooxml, embedded labels, table is Tabelle1);

TempBuckets:

LOAD * INLINE [

START, END ,bucket

0, 9, '0-9'

10, 20, '10-20'

];

TempBridge:

IntervalMatch(amount)

Load START, END Resident TempBuckets;

Left Join(TempBridge) Load * resident TempBuckets;

Left Join(FactTable) Load amount, bucket Resident TempBridge;

DROP Table TempBridge, TempBuckets;

TempBuckets:

LOAD * INLINE [

START, END ,bucket2

0, 5, '0-5'  

6, 11, '6-11'

12, 18, '12-18'

19, 20, '19-20'

];

TempBridge:

IntervalMatch(amount)

Load START, END Resident TempBuckets;

Left Join(TempBridge) Load * resident TempBuckets;

Left Join(FactTable) Load amount, bucket2 Resident TempBridge;

DROP Table TempBridge, TempBuckets;

- Marcus

[cscherer]

thanks, it works!

Could you tell me, if it`s possible to choose first between bucket and bucket2, and then to have the chosen bucket in a chart?

(I can`t open your .qvw; I´ve currently only the personal edition)

[marcus_sommer]

You mean you want to choose within the gui which bucket-dimension should be used within a chart? The easiest way for this is to use dimension-groups (document properties --> groups or within the object properties of a chart in tab dimension on the bottom left) with a cyclic group - and then you could use these group as dimension in your charts and switch between them per the small cycle-icon within the column-header by table-charts and by other chart-types mostly on the right side (there is also an option to display these icon within the title)..

- Marcus

[cscherer]

that`s it. Thank you very much!!