3 Replies Latest reply: Jul 1, 2016 9:54 AM by hippolyte ribbens RSS

    Create new column with calculated difference

    hippolyte ribbens

      Hi,

       

      I'm new to QlikView, we are trying it out for my company, to see if it can fulfill our need, up until now everything works fine, but I am currently stuck with an (apparently) very simple problem. Basically I'm trying compare the budget with the reality.

       

      I have three excel files, which look like this :

       

       

      Europe :

      name, zone = Europe, type = real, cat1, cat2, cat3, value

       

      America :

      name, zone = America, type = real, cat1, cat2, cat3, value

       

      Budget :

      zone, type = Budget, cat1, cat2, cat3, value

       

      One line could be :

      trip to Paris, Europe, real, costs, marketing, trip, 157

       

       

      I load each file and then I do drill down.

       

      What I need to do is to create a new column when loading the Budget file, named 'Difference', which would be the difference between the value of the budget line and the sum of every corresponding line from the other two table (same zone, cat1, cat2, cat3).

       

      Also if two row of Budget are the same (except for 'value'), then they would need to be summed up.

       

       

      Thanks a lot for your help, any lead or suggestion you can give me is very welcome !

       

      Edit : Added dummy data, the yellow column in 'budget' is the column I which to add on load. Tanks a lot !

        • Re: Create new column with calculated difference
          Sunny Talwar

          Would you be able to share some dummy data in those Excel file and specify what the expected output needs to be?

          • Re: Create new column with calculated difference
            hippolyte ribbens

            Managed to do something that works, here is the code if it can help anyone in the future:

             

             

            SET Actual_eurusd = 1.114;

            SET Budget_eurusd = 1.05;

             

             

            /* load of the budget for EMEA & US */

            BUDGET:

            LOAD Zone,

              Month,

              Dpt,

              [P&L Line],

              Account,

              Sum(Value) as [Solde-budget]

            FROM

            [C:\Budget.xlsx]

            (ooxml, embedded labels)

            Group by Zone, Month, Dpt, [P&L Line], Account;

             

             

            Outer Join(BUDGET)

             

             

            /* load of the file for actual for EMEA */

            EMEA:

            LOAD Account,

                 [P&L Line],

                 Dpt,

                 Month,

                 'EMEA' as Zone,

                 Sum(Solde) as [Solde-eq-eur]

            FROM

            [C:\Actual EMEA.xlsx]

            (ooxml, embedded labels, table is Sheet1)

            Group by [P&L Line], Dpt, Account, Month, Zone;

             

             

             

             

            Outer Join(BUDGET)

             

             

             

             

            /* load of the file for actual for US */

            US:

            LOAD Sum(Solde)/$(Actual_eurusd) as [Solde-eq-eur],

                 Account,

                 [P&L Line],

                 Dpt,

                 Month,

                 'North America' as Zone

            FROM

            [C:\Actual US.xlsx]

            (ooxml, embedded labels, table is Sheet1)

            Group by [P&L Line], Dpt, Account, Month, Zone;

             

             

            /* creation of the table to bu used in QV */

            FINAL:

            CrossTable(Type, Value, 5)

            LOAD Zone,

              Month,

              Dpt,

              [P&L Line],

              Account,

              -Sum([Solde-budget])/1000 as Budget,

              -Sum([Solde-eq-eur])/1000 as Actual,

              -Sum([Solde-eq-eur])/1000 + Sum([Solde-budget])/1000 as Difference

            Resident BUDGET Group By Zone, Month, Dpt, [P&L Line], Account;

             

             

            DROP Table BUDGET;