Reconciling straight table columns (Parent Child layout)

msdeepak — Fri, 30 Nov 2018 22:01:51 GMT

Hello All,

Am working on creating a control check report to reconcile columns of my straight table report.
I have a straight table which has say rows like below (arrows denote indentation i.e. parent/child)

-------------------------------------
Region Sales
-------------------------------------

Maryland 1000
--> Baltimore 800
--> --> Branch A 600
--> --> Branch B 200

--> Columbia 200
--> --> Branch X 180
--> --> Branch Y 20

Maryland 1000 is not expression total but like rest of the Sales figure is coming from DB.

So the check required is on Sales column, to show below as a column in the same straight table :

Baltimore Sales - ( Branch A Sales + Branch B Sales) = 0
Columbia Sales - ( Branch X Sales + Branch Y Sales) = 0
Maryland Sales - ( Baltimore Sales + Columbia Sales) = 0

Where there is a difference, the difference amount will be displayed.

I am able to create the parent child relationship via procedure as below:
----------------------------------------------------------------------------------------------------------------------------
Branch Id Branch Parent Branch Id
----------------------------------------------------------------------------------------------------------------------------
1 Maryland 0
2 Baltimore 1
3 Branch A 2
4 Branch B 2
5 Columbia 1
6 Branch X 5
7 Branch Y 5
----------------------------------------------------------------------------------------------------------------------------

I even tried for example, creating a set analysis variable for branch_id = {3,4} but the moment I place it as a column in the straight table it appears against rows 3 and 4 instead of row 2 which is the expected but not desired behavior. I want it to be for 2 so that I can deduct this column (children total) from the prior column (parent total).

I desire to do this in the same straight table however, am open for other suggestions too.

Any guidance/help is appreciated.

Re: Reconciling straight table columns (Parent Child layout)

chrismarlow — Tue, 04 Dec 2018 19:36:15 GMT

Hi,

Not convinced that this is the most robust solution, as very dependent on load order, but worth a try.

I extended your tables a bit & came up with the below script;

data:
LOAD * INLINE [
Branch Id , Branch , Amount
1, Maryland, 1000
2, Baltimore, 800
3, Branch A, 600
4, Branch B, 100
5, Columbia, 200
6, Branch X, 180
7, Branch Y, 20
];

parent_mapping:
LOAD * INLINE [
    Parent Branch Id , Branch Id
    0, 1
    1, 2
    1, 5
    2, 3
    2, 4
    3, 3
    4, 4
    5, 6
    5, 7
    6, 6
    7, 7
];

Then the following chart shows the 700 vs 800 on Baltimore aggregation;

Cheers,

Chris.

topic Reconciling straight table columns (Parent Child layout) in QlikView

Reconciling straight table columns (Parent Child layout)

Re: Reconciling straight table columns (Parent Child layout)