topic Extend variable classes in QlikView

Extend variable classes

cscherer — Fri, 05 Aug 2016 11:53:03 GMT

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

Re: Extend variable classes

marcus_sommer — Fri, 05 Aug 2016 13:31:55 GMT

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

Re: Extend variable classes

vinieme12 — Fri, 05 Aug 2016 14:22:20 GMT

**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

];

Re: Extend variable classes

vinieme12 — Fri, 05 Aug 2016 14:35:04 GMT

let me know if it worked for u

Re: Extend variable classes

cscherer — Mon, 08 Aug 2016 06:42:42 GMT

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;

Re: Extend variable classes

cscherer — Mon, 08 Aug 2016 06:43:41 GMT

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

Regards

Re: Extend variable classes

marcus_sommer — Mon, 08 Aug 2016 10:27:56 GMT

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

Re: Extend variable classes

cscherer — Mon, 08 Aug 2016 10:44:01 GMT

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)

Re: Extend variable classes

marcus_sommer — Mon, 08 Aug 2016 12:53:29 GMT

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

Re: Extend variable classes

cscherer — Mon, 08 Aug 2016 13:55:36 GMT

that`s it. Thank you very much!!