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: 
Not applicable

Create new column with calculated difference

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 !

1 Solution

Accepted Solutions
Not applicable
Author

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

(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

(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

(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;

View solution in original post

3 Replies
sunny_talwar

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

Not applicable
Author

Yes you are wright, I should have included dummy data straight away, I modified my original post to include the files as you suggested.

Not applicable
Author

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

(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

(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

(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;