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

[avinashelite]

Hi Senarath,

To get the number of day in that month Try like this:

=Day(MonthEnd(Today()))

or if you applying for a field then

=Day(MonthEnd(Date_feild))

[PrashantSangle]

Hi,

Once row duplicated after that,

What are you trying to acheive??

Regards

[anbu1984]

Load *,Amount/MthEnd As NewAmt,IterNo() As Day While IterNo() <= MthEnd;
Load *,Day(MonthEnd(Month)) As MthEnd;
Load Field1,Field2,Field3,Date#(Month,'MMM-YY') As Month,Amount,Amount2 Inline [
Field1,Field2,Field3,Month,Amount,Amount2
FactoryA,JIT customers,NorthArea,Feb-15,1600.00,175
FactoryB,Direct customers,NorthArea,Feb-15,1750.00,140 ];

[senarath]

Hi Max,

I need to monitor daily actual amount with daily allocated budget amount.

thanks

[senarath]

Hi anbu,

let me try your suggestion and come back to you.

thanks

[senarath]

This is my original row

Factory	Field1	Field2	Month	Amount1	Amount2	Amount3
ABC	VAL5	BB	4/1/2014	15,924,974	0	15,924,974

This is my final requirement

Factory	Field1	Field2	Date	Amount1	Amount2	Amount3
ABC	VAL5	BB	4/1/2014	20	40	319,080
ABC	VAL5	AA	4/1/2014	13	25	261,065
ABC	VAL5	BB	4/2/2014	20	40	319,080
ABC	VAL5	AA	4/2/2014	13	25	261,065
ABC	VAL5	BB	4/3/2014	20	40	319,080
ABC	VAL5	AA	4/3/2014	13	25	261,065
ABC	VAL5	BB	4/4/2014	20	40	319,080
ABC	VAL5	AA	4/4/2014	13	25	261,065
ABC	VAL5	BB	4/5/2014	20	40	319,080
ABC	VAL5	AA	4/5/2014	13	25	261,065
ABC	VAL5	BB	4/6/2014	20	40	319,080
ABC	VAL5	AA	4/6/2014	13	25	261,065
ABC	VAL5	BB	4/7/2014	20	40	319,080
ABC	VAL5	AA	4/7/2014	13	25	261,065
ABC	VAL5	BB	4/8/2014	20	40	319,080
ABC	VAL5	AA	4/8/2014	13	25	261,065
ABC	VAL5	BB	4/9/2014	20	40	319,080
ABC	VAL5	AA	4/9/2014	13	25	261,065
ABC	VAL5	BB	4/10/2014	20	40	319,080
ABC	VAL5	AA	4/10/2014	13	25	261,065
ABC	VAL5	BB	4/11/2014	20	40	319,080
ABC	VAL5	AA	4/11/2014	13	25	261,065
ABC	VAL5	BB	4/12/2014	20	40	319,080
ABC	VAL5	AA	4/12/2014	13	25	261,065
ABC	VAL5	BB	4/13/2014	20	40	319,080
ABC	VAL5	AA	4/13/2014	13	25	261,065
ABC	VAL5	BB	4/14/2014	20	40	319,080
ABC	VAL5	AA	4/14/2014	13	25	261,065
ABC	VAL5	BB	4/15/2014	20	40	319,080
ABC	VAL5	AA	4/15/2014	13	25	261,065
ABC	VAL5	BB	4/16/2014	20	40	319,080
ABC	VAL5	AA	4/16/2014	13	25	261,065
ABC	VAL5	BB	4/17/2014	20	40	319,080
ABC	VAL5	AA	4/17/2014	13	25	261,065
ABC	VAL5	BB	4/18/2014	20	40	319,080
ABC	VAL5	AA	4/18/2014	13	25	261,065
ABC	VAL5	BB	4/19/2014	20	40	319,080
ABC	VAL5	AA	4/19/2014	13	25	261,065
ABC	VAL5	BB	4/20/2014	20	40	319,080
ABC	VAL5	AA	4/20/2014	13	25	261,065
ABC	VAL5	BB	4/21/2014	20	40	319,080
ABC	VAL5	AA	4/21/2014	13	25	261,065
ABC	VAL5	BB	4/22/2014	20	40	319,080
ABC	VAL5	AA	4/22/2014	13	25	261,065
ABC	VAL5	BB	4/23/2014	20	40	319,080
ABC	VAL5	AA	4/23/2014	13	25	261,065
ABC	VAL5	BB	4/24/2014	20	40	319,080
ABC	VAL5	AA	4/24/2014	13	25	261,065
ABC	VAL5	BB	4/25/2014	20	40	319,080
ABC	VAL5	AA	4/25/2014	13	25	261,065
ABC	VAL5	BB	4/26/2014	20	40	319,080
ABC	VAL5	AA	4/26/2014	13	25	261,065
ABC	VAL5	BB	4/27/2014	20	40	319,080
ABC	VAL5	AA	4/27/2014	13	25	261,065
ABC	VAL5	BB	4/28/2014	20	40	319,080
ABC	VAL5	AA	4/28/2014	13	25	261,065
ABC	VAL5	BB	4/29/2014	20	40	319,080
ABC	VAL5	AA	4/29/2014	13	25	261,065
ABC	VAL5	BB	4/30/2014	20	40	319,080
ABC	VAL5	AA	4/30/2014	13	25	261,065

[jagan · Accepted solution]

Hi,

Try this script

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 Inline [ 

Factory,Customer,Area,Month,Amount, Amount2 

FactoryA,JIT customers,NorthArea,Feb-15,1600.00, 175

FactoryB,Direct customers,NorthArea,Feb-15,1750.00, 140];

Hope this helps you.

Regards,

jagan.

[jonathandienst]

Some clarification is needed

Why do you have two rows for each date?

How is Amount3 split between the 2 dates?

How do Amount 1 and Amount 2 relate to the data in the original row at all?

It would help if the data in your original post and the the data in this post showed the same thing...

[senarath]

Hi Jonathan,

Sorry, 01. Field2 should go as BB and AA for each two rows.( I CORRECTED MY POST)

02. We have monthly budget and daily actual where we need to monitor daily performance

03. Amount1 and Amount2 are equally divided by number of days in each month.

Hope above is clear

thanks