Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Need to lock a currency for a certain scenario and period.

PeriodScenario1Scenario2
201501AA
201502AB
201503AN
201504N
201505

Hi, I have a PnL with different scenarios for forecasting and with consolidation in to a single currency with revenue in different currencies.

I have already implemented that you can choose which currency to map with A (Actuals FX Rates) and B (Budget FX Rates)  but know I need to add a third option which would be N. What N should do is take the currency valid for the period where its inserted and apply that to the rest of the years coming periods.

That means that for scenario1 it should be 1,323500 from march and the rest of 2015.

For Scenario2 it should be 1,354995 from May and until the rest of 2015. I've tried with a few intern() combined with if conditions in the script but can't really get it to work.

Thankful for any help

I've attached an example file in Excel that explains a bit better what I'm trying to achieve.

PeriodFXRate
201501

 

1,268000
201502

 

1,117393
201503

 

1,354995
201504

 

1,323500
201505

 

1,357140
3 Replies

Re: Need to lock a currency for a certain scenario and period.

Would you be able to share a sample with expected output? I am not entirely sure what you are trying to achieve here.

Best,

Sunny

Not applicable

Re: Need to lock a currency for a certain scenario and period.

Hi Sunny,

Thanks for the reply. I realised as I had posted this it wasn't really clear .

I've attached an example that should make it easier to understand what I'm trying to do.

Thanks for pointing that out! 

Re: Need to lock a currency for a certain scenario and period.

This is what you wanted?

Capture.PNG

Script:

Table:

LOAD PeriodId,

    Scenario1,

    Scenario2,

    If(Len(Trim(Scenario1)) = 0, Peek('NewScenario1'), Scenario1) as NewScenario1,

    If(Len(Trim(Scenario2)) = 0, Peek('NewScenario2'), Scenario2) as NewScenario2

FROM

Community_170470.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(Table)

LOAD PeriodId,

    FX

FROM

Community_170470.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(Table)

LOAD PeriodId,

    BudgetScenario1,

    BudgetScenario2

FROM

Community_170470.xlsx

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD FX as Scenario1FX

Resident Table

Where Scenario1 = 'N';

LET vScenario1FX = Peek('Scenario1FX');

DROP Table Temp;

Temp:

LOAD FX as Scenario2FX

Resident Table

Where Scenario2 = 'N';

LET vScenario2FX = Peek('Scenario2FX');

DROP Table Temp;

FinalTable:

LOAD *,

  If(NewScenario1 <> 'N', BudgetScenario1*FX, BudgetScenario1 * $(vScenario1FX)) as ResultScenario1,

  If(NewScenario2 <> 'N', BudgetScenario2*FX, BudgetScenario2 * $(vScenario2FX)) as ResultScenario2

Resident Table;

DROP Table Table;

Also attaching the qvw for your reference.

HTH

Best,

Sunny

Community Browser