days creation based on one day in a month

[sakamsureshbabu]

Hi

i have one table like below

data	target
1/1/2014	30
2/1/2014	30
3/1/2014	30
4/1/2014	30
5/1/2014	30
6/1/2014	30

date format M/D/YYYY

but i want create some another field like

data	target	date1	target1
1/1/2014	30	1/1/2014	30/days in month
		1/2/2014	30/days in month
		1/3/2014	30/days in month
		1/4/2014	30/days in month
		1/5/2014	30/days in month
		1/6/2014	30/days in month
		1/7/2014	30/days in month
		1/8/2014	30/days in month
		1/9/2014	30/days in month
		1/10/2014	30/days in month

how can i create like this........

[oknotsen]

I am not sure if I understand you.

Do you want to make a Pivot table or are you looking for a way to combine data in one field?

[sakamsureshbabu]

Hi Onno

first table is my sours table

that table i want convert like second table

[sasiparupudi1]

try this script

Data:

load  date#(data,'D/M/YYYY') as data,target,

date(date#(data,'D/M/YYYY'),'M/D/YYYY') as data1,

target&'/days in month' as target1

inline [

data,target

1/1/2014,30

2/1/2014,30

3/1/2014,30

4/1/2014,30

5/1/2014,30

6/1/2014,30

30/1/2014,30

];

[sakamsureshbabu]

in my source table i have only two fields those are date and target

table like below

data	target
1/1/2014	30
2/1/2014	30
3/1/2014	30
4/1/2014	30
5/1/2014	30
6/1/2014	30

my date format M/D/YYYY

there given month vise target but i want to convert target value into daily base and store those information day base like below table format 

data	target	date1	target1
1/1/2014	30	1/1/2014	30/days in month
		1/2/2014	30/days in month
		1/3/2014	30/days in month
		1/4/2014	30/days in month
		1/5/2014	30/days in month
		1/6/2014	30/days in month
		1/7/2014	30/days in month
		1/8/2014	30/days in month
		1/9/2014	30/days in month
		1/10/2014	30/days in month

this table containing 4 fields

how can i create this table ............ 

[prashantbaste]

Hi

You can create a Date field having all dates and join it with your data. This will give you exactly the output you have shown above.

Hope this will be helpful for you.

--

Regards,

Prashant P Baste

[sakamsureshbabu]

Hi

my date field will update automatically when post in server also

[tresesco]

Load
Date(Date+IterNo()-1) as Date,
Month(Date(Date+IterNo()-1)) as Month,
target/Day(MonthEnd(Date)) as Target,

target
While Date+IterNo()-1<=MonthEnd(Date);

Load
Date#(date, 'M/D/YYYY') as Date,
target inline [
date,target

1/1/2014,30

2/1/2014,30

3/1/2014,30

4/1/2014,30

5/1/2014,30

6/1/2014,30

];

[prashantbaste]

Hi

You can create Dynamic datefield which will update on every reload of your data.

Ex. -

Let varMinDate = Num(Peek('minDate'));

Let varMaxDate = Num(Peek('maxDate'));

TempCalendar:

LOAD $(MinDateRange) + Iterno()-1 As Num,

	IterNo() as ITERCOUNT,
	Date($(MinDateRange) + IterNo() - 1) as DateField
	AutoGenerate 1

While $(MinDateRange) + IterNo() -1 <= $(MaxDateRange);

As shown in above code... Date will be created from your data only.

Hope this will be helpful for you.

--

Regards,

Prashant P Baste

[sasiparupudi1]

Data:

LOAD data,target,

date(data+IterNo()-1,'M/D/YYYY') AS date1,

ceil(MonthEnd(data+IterNo()-1,0)-MonthStart(data+IterNo()-1,0))&'/days in month' as target1

while data+IterNo()-1<=MonthEnd(data,0);

load  date#(data,'M/D/YYYY') as data ,target

inline [

data,target

1/1/2014,30

2/1/2014,30

3/1/2014,30

4/1/2014,30

5/1/2014,30

6/1/2014,30

];