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

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

senarath — Tue, 07 Apr 2015 04:35:16 GMT

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

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

avinashelite — Tue, 07 Apr 2015 04:50:11 GMT

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

PrashantSangle — Tue, 07 Apr 2015 04:59:42 GMT

Hi,

Once row duplicated after that,

What are you trying to acheive??

Regards

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

anbu1984 — Tue, 07 Apr 2015 05:07:56 GMT

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

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

senarath — Tue, 07 Apr 2015 05:23:14 GMT

Hi Max,

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

thanks

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

senarath — Tue, 07 Apr 2015 05:25:38 GMT

Hi anbu,

let me try your suggestion and come back to you.

thanks

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

senarath — Tue, 07 Apr 2015 05:33:00 GMT

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

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

jagan — Tue, 07 Apr 2015 05:42:46 GMT

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.

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

jonathandienst — Tue, 07 Apr 2015 05:46:52 GMT

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

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

senarath — Tue, 07 Apr 2015 06:01:28 GMT

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

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

senarath — Tue, 07 Apr 2015 10:01:10 GMT

Thanks all for your valuable time.....

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

senarath — Tue, 07 Apr 2015 11:58:24 GMT

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

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

jagan — Wed, 08 Apr 2015 02:06:37 GMT

Hi,

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

Regards,

jagan.

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

Anonymous — Tue, 12 Dec 2017 00:01:41 GMT

Hi Jagan,

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

regards

Angelo