6 Replies Latest reply: Jun 14, 2011 2:24 PM by Jeroen Vuurens RSS

    Pivot Table. Help

    Medea Linanda

      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?

        • Re: Pivot Table. Help

          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

            • Re: Pivot Table. Help
              Medea Linanda

              Hi Jeroen,

               

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

                • Re: Pivot Table. Help
                  Johannes Sunden

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

                  • Re: Pivot Table. Help

                    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.

                      • Re: Pivot Table. Help
                        Medea Linanda

                        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

                          • Re: Pivot Table. Help

                            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;