search

afs1jes / Clarins-US-IR-Migration

0 stars 0 forks source link

Load Finance - USA #87

Open afs1jes opened 1 year ago

afs1jes commented 1 year ago

Updated SQL grid on FlipOnLoad sheet. Added 'dummy' column to the left of the grid that allows AccountNumber formula to get copied down correctly.

New SQL query: SELECT '' AS AccountNumber, AccountsName, AccountType FROM [Dimension].[Accounts] where AccountType = '-1'

RobSarajian commented 1 year ago

On the 4 load Finance templates - the flip is not working . All of the accounts above net sales need to be flipped as well as the accounts below Op profit.

AVHuntley commented 1 year ago

This one took some detective work, but I think I found the culprit. It was a VALUE() formula wrapping parameters in the formula in column AO (AccountFunction) on the Load sheet. This was causing the VLOOKUP in that formula to try (and fail to) compare a number with a string of digits. Easy for human, hard for machine.

In the old system this seemed to work, but in the new system, SQL table values are retrieved as strings. (In fact, we made it this way to accommodate those Dept & Brand MAP values that have leading zeros.)

The easy solution in this case is to remove the VALUE wrapping in the formula in that column. So this... =IF(A2="Balance Sheet","BS Function",IF(ISERROR(VLOOKUP(VALUE(C2),'Account to Function Mapping'!DataSet2,9,FALSE)),"Acct "&C2,IF(VLOOKUP(VALUE(C2),'Account to Function Mapping'!DataSet2,9,FALSE)=0,"000",VLOOKUP(VALUE(C2),'Account to Function Mapping'!DataSet2,9,FALSE))))

Becomes this... =IF(A2="Balance Sheet","BS Function",IF(ISERROR(VLOOKUP(C2,'Account to Function Mapping'!DataSet2,9,FALSE)),"Acct "&C2,IF(VLOOKUP(C2,'Account to Function Mapping'!DataSet2,9,FALSE)=0,"000",VLOOKUP(C2,'Account to Function Mapping'!DataSet2,9,FALSE))))

I have made this change to all the templates called "Load Finance..." and "Load Chain...".