Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
senarath
Creator III
Creator 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
jagan
Luminary Alumni
Luminary Alumni

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
anbu1984
Master III
Master III

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
Creator III
Creator III
Author

Hi Max,

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

thanks

senarath
Creator III
Creator III
Author

Hi anbu,

let me try your suggestion and come back to you.

thanks

senarath
Creator III
Creator III
Author

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
jagan
Luminary Alumni
Luminary Alumni

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
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

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