topic Re: Creating a Calculated Table in QlickView in QlikView

Creating a Calculated Table in QlickView

Thu, 16 Apr 2015 15:10:19 GMT

So what I have currently is a table with 3 fields: Dates (detailed dates spanning over 5 years), Sale (spanning over 0-3000), Quantity (spanning 0-40).

What I want is to create a table that has 5 fields.

Field 1: Year

Field 2: Sales Lower Bound

Field 3: Sales Upper Bound

Field 4: Sum of sales (within the bounds for that year)

Field 5: Sum of Quantity (within the bounds for that year)

Where the bounds are predetermined to be 0, 25, 50, 100, 150, 200, 300, 500, 1000, 2000, 3000.

So for example if I want the line it will look like this

Year = 2005

Sales Lower Bound = 0

Sales Upper Bound = 25

Sum of Sales = sum(if(Sale >= 0 and Sales < 25 and Year = 2005, Sale*Quantity, 0))

Sum of Quantity = sum(if(Sale >= 0 and Sales < 25 and Year = 2005, Quantity, 0))

I can create all (5x10=50) lines as separate 1 line tables and concatenate them after but there has to be a better way.

Any help

Re: Creating a Calculated Table in QlickView

oknotsen — Fri, 17 Apr 2015 12:24:40 GMT

I would suggest to make an inline table in your script that contains the limits of those bounds.

Example:

Bounds:
LOAD * INLINE [
BoundName, BeginBound, EndBound
0-25, 0, 25
25-50, 25, 50
50-100, 50, 100
100-150, 100, 150
150+, 150
];

Make this list complete; I think you get the idea.

Next, do an interval match of this sales table with that bounds table.

Left JOIN (YourSalesTable)
IntervalMatch (SalesResults)
Load
BeginBound,
EndBound
Resident Bounds;

Now you have a Bounds dimension in your data model, you can use this as a dimension in your table to get the numbers you want.

Re: Creating a Calculated Table in QlickView

Fri, 17 Apr 2015 17:22:25 GMT

An interesting a good approach. Definite more elegant. Thank you.