topic Re: days creation based on one day in a month in QlikView

days creation based on one day in a month

sakamsureshbabu — Fri, 24 Jul 2015 11:19:40 GMT

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

Re: days creation based on one day in a month

oknotsen — Fri, 24 Jul 2015 11:28:36 GMT

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?

Re: days creation based on one day in a month

sakamsureshbabu — Fri, 24 Jul 2015 11:39:22 GMT

Hi Onno

first table is my sours table

that table i want convert like second table

Re: days creation based on one day in a month

sasiparupudi1 — Fri, 24 Jul 2015 11:51:16 GMT

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

];

Re: days creation based on one day in a month

sakamsureshbabu — Fri, 24 Jul 2015 12:08:07 GMT

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

Re: days creation based on one day in a month

prashantbaste — Fri, 24 Jul 2015 12:22:42 GMT

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

Re: days creation based on one day in a month

sakamsureshbabu — Fri, 24 Jul 2015 12:33:11 GMT

my date field will update automatically when post in server also

Re: days creation based on one day in a month

tresB — Fri, 24 Jul 2015 12:41:21 GMT

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

];

Re: days creation based on one day in a month

prashantbaste — Fri, 24 Jul 2015 12:51:03 GMT

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

Re: days creation based on one day in a month

sasiparupudi1 — Fri, 24 Jul 2015 12:57:32 GMT

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

];

Re: days creation based on one day in a month

sakamsureshbabu — Mon, 27 Jul 2015 06:20:46 GMT

Hi Sasidhar

it is working Thank U......

Re: days creation based on one day in a month

sasiparupudi1 — Mon, 27 Jul 2015 07:07:28 GMT

please close the thread if you got your answer

Thx

Sasi