Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

senarath
Contributor III

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

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

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.

View solution in original post

13 Replies

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

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))

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

Hi,

Once row duplicated after that,

What are you trying to acheive??

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
anbu1984
Honored Contributor III

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

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
Contributor III

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

Hi Max,

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

thanks

senarath
Contributor III

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

Hi anbu,

let me try your suggestion and come back to you.

thanks

senarath
Contributor III

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

This is my original row

     

FactoryField1Field2MonthAmount1Amount2Amount3
ABCVAL5BB4/1/201415,924,974015,924,974

This is my final requirement

     

FactoryField1Field2DateAmount1Amount2Amount3
ABCVAL5BB4/1/20142040319,080
ABCVAL5AA4/1/20141325261,065
ABCVAL5BB4/2/20142040319,080
ABCVAL5AA4/2/20141325261,065
ABCVAL5BB4/3/20142040319,080
ABCVAL5AA4/3/20141325261,065
ABCVAL5BB4/4/20142040319,080
ABCVAL5AA4/4/20141325261,065
ABCVAL5BB4/5/20142040319,080
ABCVAL5AA4/5/20141325261,065
ABCVAL5BB4/6/20142040319,080
ABCVAL5AA4/6/20141325261,065
ABCVAL5BB4/7/20142040319,080
ABCVAL5AA4/7/20141325261,065
ABCVAL5BB4/8/20142040319,080
ABCVAL5AA4/8/20141325261,065
ABCVAL5BB4/9/20142040319,080
ABCVAL5AA4/9/20141325261,065
ABCVAL5BB4/10/20142040319,080
ABCVAL5AA4/10/20141325261,065
ABCVAL5BB4/11/20142040319,080
ABCVAL5AA4/11/20141325261,065
ABCVAL5BB4/12/20142040319,080
ABCVAL5AA4/12/20141325261,065
ABCVAL5BB4/13/20142040319,080
ABCVAL5AA4/13/20141325261,065
ABCVAL5BB4/14/20142040319,080
ABCVAL5AA4/14/20141325261,065
ABCVAL5BB4/15/20142040319,080
ABCVAL5AA4/15/20141325261,065
ABCVAL5BB4/16/20142040319,080
ABCVAL5AA4/16/20141325261,065
ABCVAL5BB4/17/20142040319,080
ABCVAL5AA4/17/20141325261,065
ABCVAL5BB4/18/20142040319,080
ABCVAL5AA4/18/20141325261,065
ABCVAL5BB4/19/20142040319,080
ABCVAL5AA4/19/20141325261,065
ABCVAL5BB4/20/20142040319,080
ABCVAL5AA4/20/20141325261,065
ABCVAL5BB4/21/20142040319,080
ABCVAL5AA4/21/20141325261,065
ABCVAL5BB4/22/20142040319,080
ABCVAL5AA4/22/20141325261,065
ABCVAL5BB4/23/20142040319,080
ABCVAL5AA4/23/20141325261,065
ABCVAL5BB4/24/20142040319,080
ABCVAL5AA4/24/20141325261,065
ABCVAL5BB4/25/20142040319,080
ABCVAL5AA4/25/20141325261,065
ABCVAL5BB4/26/20142040319,080
ABCVAL5AA4/26/20141325261,065
ABCVAL5BB4/27/20142040319,080
ABCVAL5AA4/27/20141325261,065
ABCVAL5BB4/28/20142040319,080
ABCVAL5AA4/28/20141325261,065
ABCVAL5BB4/29/20142040319,080
ABCVAL5AA4/29/20141325261,065
ABCVAL5BB4/30/20142040319,080
ABCVAL5AA4/30/20141325261,065
MVP & Luminary
MVP & Luminary

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

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.

View solution in original post

MVP
MVP

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

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...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
senarath
Contributor III

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

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