Pivot Table. Help

[#1]

Hi all,

There are 2 tables here:

Table1:

ID - Description - FromRange - ToRange

1 - TOTAL OPERATING EXPENSES - 60000000 - 69999999

2 - TOTAL NON OPERATING EXPENSES - 70000000 - 79999999

S1 - TOTAL EXPENSES - 60000000 - 79999999

3 - TOTAL OPERATING REVENUE - 80000000 - 89999999

4 - TOTAL NON OPERATING REVENUE - 90000000 - 99999999

S2 - TOTAL REVENUE - 80000000 - 99999999

Table2:

ID - Description

60000001 - 50000

60000002 - 100000

80000999 - 20000

Questions:

How to create pivot table and set S1 is total of ID 1 & 2 and set S2 is total of ID 3 & 4?

[#2]

Perhaps this solves it:

TOTAL:

LOAD * INLINE [

TOTAL_id, TOTAL_description, TOTAL_from_range, TOTAL_to_range

S1, TOTAL EXPENSES, 60000000, 79999999

S2, TOTAL REVENUE, 80000000, 99999999];

SUBTOTAL:

LOAD * INLINE [

SUBTOTAL_id, SUBTOTAL_description, SUBTOTAL_from_range, SUBTOTAL_to_range

1, TOTAL OPERATING EXPENSES, 60000000, 69999999

2, TOTAL NON OPERATING EXPENSES, 70000000, 79999999

3, TOTAL OPERATING REVENUE, 80000000, 89999999

4, TOTAL NON OPERATING REVENUE, 90000000, 99999999];

IntervalMatch (SUBTOTAL_from_range)

LOAD TOTAL_from_range, TOTAL_to_range

RESIDENT TOTAL;

TABLE2:

LOAD * INLINE [

id, amount

60000001, 50000

60000002, 100000

80000999, 20000];

IntervalMatch (id)

LOAD SUBTOTAL_from_range, SUBTOTAL_to_range

RESIDENT SUBTOTAL;

Although this creates a structure with synthetic keys, the outcomes should be correct. If you have problems with the synthetic keys you might want to load it into a lean structure with approriate foreign keys. This is very easy to do from SQL, not so much in QV.

hth Jeroen

[#3]

Hi Jeroen,

Appreciate your reply but is there any ways to not hard code it? Thanks

[Anonymous]

Maybe you can explain what you expect to see in the Pivot table? I'm a bit confused with the description at this point.

[#5]

Not sure what you mean by hard code. But if you refer to splitting the data into tables TOTAL and SUBTOTAL, I think that's the only way to do it, to prevent circular references.

You can ofc load it from one and the same table, but like I said, flexible loading is much easier from SQL, even so much you might consider staging it in a database b4 importing into QV.

[#6]

Thanks for the input.. Im trying to split the table from source table into 2 destination tables without type the data on the script:

Source Table:

ID - Description - FromRange - ToRange

1 - TOTAL OPERATING EXPENSES - 60000000 - 69999999

2 - TOTAL NON OPERATING EXPENSES - 70000000 - 79999999

S1 - TOTAL EXPENSES - 60000000 - 79999999

3 - TOTAL OPERATING REVENUE - 80000000 - 89999999

4 - TOTAL NON OPERATING REVENUE - 90000000 - 99999999

S2 - TOTAL REVENUE - 80000000 - 99999999

Destination Table:

TitleID - Description

S1 - TOTAL EXPENSES

S2 - TOTAL REVENUE

TitleID - SubTitleID - Description - FromRange - ToRange

S1 - 1 - TOTAL OPERATING EXPENSES - 60000000 - 69999999

S1 - 2 - TOTAL NON OPERATING EXPENSES - 70000000 - 79999999

S2 - 3 - TOTAL OPERATING REVENUE - 80000000 - 89999999

S2 - 4 - TOTAL NON OPERATING REVENUE - 90000000 - 99999999

[#7]

Well, assuming the TOTAL fields always have the bigger id interval than the containing SUBTOTALS, you can track down the biggest intervals in the SOURCE table. I'm not sure if you can do it in one step with QV, but I ended up using a temporary table, discarding that later on. Looks good to me, what do you think?

SOURCE:

LOAD * INLINE [

id, description, from_range, to_range

1, TOTAL OPERATING EXPENSES, 60000000, 69999999

2, TOTAL NON OPERATING EXPENSES, 70000000, 79999999

3, TOTAL OPERATING REVENUE, 80000000, 89999999

4, TOTAL NON OPERATING REVENUE, 90000000, 99999999

S1, TOTAL EXPENSES, 60000000, 79999999

S2, TOTAL REVENUE, 80000000, 99999999];

IDRANGE:

LOAD

from_range as from,

max(to_range) as to // will find the upper bound for id interval

RESIDENT SOURCE

GROUP BY from_range ;

TOTAL:

LOAD

min(from) as TOTAL_from_range, // will find the lower bound for id interval

to as TOTAL_to_range

RESIDENT IDRANGE

GROUP BY to;

JOIN // adding other source fields to total table

LOAD

id                as TOTAL_id,

description as TOTAL_description,

from_range      as TOTAL_from_range,

to_range      as TOTAL_to_range

RESIDENT SOURCE

WHERE from_range = Lookup('TOTAL_from_range', 'TOTAL_to_range', to_range, 'TOTAL')

// matching SOURCE record to TOTAL record

;

SUBTOTAL:

LOAD

id as SUBTOTAL_id,

description          as SUBTOTAL_description,

from_range           as SUBTOTAL_from_range,

to_range          as SUBTOTAL_to_range

RESIDENT SOURCE

WHERE not from_range = Lookup('TOTAL_from_range', 'TOTAL_to_range', to_range, 'TOTAL')

// if it's not a TOTAL, then it's a SUBTOTAL

;

DROP TABLE IDRANGE, SOURCE;

IntervalMatch (SUBTOTAL_from_range)

LOAD

TOTAL_from_range,

TOTAL_to_range

RESIDENT TOTAL;

TABLE2:

LOAD * INLINE [

id, amount

60000001, 50000

60000002, 100000

80000999, 20000];

IntervalMatch (id)

LOAD SUBTOTAL_from_range,

SUBTOTAL_to_range

RESIDENT SUBTOTAL;