Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
msdeepak
Contributor II
Contributor II

Reconciling straight table columns (Parent Child layout)

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.

1 Reply
chrismarlow
Specialist II
Specialist II

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;
20181204_3.png
 
Cheers,
 
Chris.