How to split a single row value into multiple rows with equal value

[senarath]

Hi All,

I need to link actual amounts with Budgets for a report. All these time, we have been handling this manually by splitting cells into thousands of rows manually.

Thanks to Qlikview, I hope above procedure would no longer be required and could be handled by Qlikview.

Budget:

Field1,      Field2,               Field3,         Month      Amount     Amount2

FactoryA  JIT customers     NorthArea    Feb-15      1,600.00     175

FactoryB  Direct customersNorthArea    Feb-15      1,750.00     140

...etc....     ...............          ...............   ........         ............

Actuals:

Field1,      Field2,               Field3,         Date           Amount     Amount2

FactoryA  JIT customers     NorthArea    01-Feb-15      175.00     6

FactoryA  JIT customers     NorthArea    02-Feb-15      140.00     2

..etc....     ...............          ...............   ........         ............

I have budgets month wise and actual data come on a daily basis and requirement is to report on a daily basis.

Can I handle splitting of Budget rows into number of days in a month (28 rows in Feb) by equal amounts (1,600/28 and 175/28) AND also it should be unique for all fields because we have to review budgets all Fields wise.

Many thanks

Senarath

[senarath]

Thanks all for your valuable time.....

[senarath]

Hi Jagan,

One more point. When I give from instead of inline, data do not get loaded.

Budget:

Load Factory,Customer,Area,Amount,Month,Amount2/DaysInMonth As Amount2, Date(Date#(Month,'MMM-YY') + IterNo() - 1) As Date

While IterNo() <= DaysInMonth;

Load Factory,Customer,Area,Month, Day(MonthEnd(Date#(Month,'MMM-YY'))) As DaysInMonth, Amount, Amount2

FROM

[Budget_L_2015-16.xlsx]

(ooxml, embedded labels, table is HK);

Am I missing something here. Script completes but no data picked up

thanks

[jagan]

Hi,

I think the issue is with the Date format, check the date format you are getting in the Qlikview from Excel.

Regards,

jagan.

[Anonymous]

Hi Jagan,

Do you happen to know how to skip public holidays and weekends during the load?

regards

Angelo