topic Re: General Date Scripting help in QlikView

General Date Scripting help

Fri, 21 Nov 2014 09:57:22 GMT

More a general scripting query, rather than a QV specific query.

However it is really wearing me down, any help would be greatly apprciated.

I have the columns MSN, Date and Day Dif, I want to add the columns calculating the missing dates, in the format below.

MSN	Date	Day Dif	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
116403	05/01/2015	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	01/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	02/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	03/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	04/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	05/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	06/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	08/01/2014	2	6.44%	0	0	0	0	0	0	0	0	0	0	0
116404	09/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0
116404	02/02/2014	24	70.90%	7.14%	0	0	0	0	0	0	0	0	0	0
116404	03/02/2014	1	0	3.57%	0	0	0	0	0	0	0	0	0	0
116404	04/02/2014	1	0	3.57%	0	0	0	0	0	0	0	0	0	0
116404	05/02/2014	1	0	3.57%	0	0	0	0	0	0	0	0	0	0
116404	06/02/2014	1	0	3.57%	0	0	0	0	0	0	0	0	0	0
116404	23/12/2014	320	0	78.57%	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	70.97%
116404	24/12/2014	1	0	0	0	0	0	0	0	0	0	0	0	3.22%
116404	25/12/2014	1	0	0	0	0	0	0	0	0	0	0	0	3.22%
116404	26/12/2014	1	0	0	0	0	0	0	0	0	0	0	0	3.22%
116404	27/12/2014	1	0	0	0	0	0	0	0	0	0	0	0	3.22%
116404	05/01/2015	9	16.10%	0	0	0	0	0	0	0	0	0	0	12.88%
116405	01/01/2014	1	3.22%	0	0	0	0	0	0	0	0	0	0	0

Regards

Re: General Date Scripting help

datanibbler — Fri, 21 Nov 2014 10:10:10 GMT

Hi Richard,

sorry, would you mind elaborating a bit on exactly what you want to do? I'm guessing ...

Re: General Date Scripting help

Fri, 21 Nov 2014 10:18:01 GMT

good point, I have been looking a this so long , I almost expect every one else to understand .

I am trying to calculate how many days are missing between the date and previous(date) in table, and then calculate which months those missing dates occur. This can be expressed as a percentage of the month or number of days in said month and added to the origional table as above. I will then use table later on in the script.

I hope this makes sense

Re: General Date Scripting help

Fri, 21 Nov 2014 17:28:09 GMT

Try this, Peek would fetch previous records and returns zero, if there isn't any.

Load

Date(Floor(Date))-Peek(Date(Floor(Date) as Dates_Diff,

(Date(Floor(Date))-Peek(Date(Floor(Date) /Floor(MonthEnd(Date))-Floor(MonthStart(Date))+1)*100 as [% of Days Missing]

From Table

Order by Date.

Re: General Date Scripting help

Fri, 21 Nov 2014 20:05:03 GMT

Use Peek or Previous Function to get the previous row value.