Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Hi Jeroen,
Appreciate your reply but is there any ways to not hard code it? Thanks
Maybe you can explain what you expect to see in the Pivot table? I'm a bit confused with the description at this point.
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.
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
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;