Correct MONTHYEAR combination not showing using IterNo

mrainford — Tue, 09 Aug 2016 15:55:23 GMT

I have the following load script in which I need to have the appropriate MONTHYEAR displayed between each phase_start and phase_end. Notice how PHASE_ID 2 should display MONTH_YEAR value = Jul 2016 and Jun 2016. PHASE_ID 3 should display July 2016 and Aug 2016. PHASE_ID 4 = Aug,Sep, Oct. What am I doing wrong in my load script?

PPJ_ID	UNITS OF WORK	TOTAL MONTHS	TOTAL UNITS	NUMBER_OF_DAYS	PHASE_ID	PHASE_START	PHASE_END	PHASE DURATION	UNIT_IN_PHASE	MONTH_YEAR
140056	40	14	40	141	1	24/06/2016	24/06/2016	0	0	Jun 2016	0.67
					2	24/06/2016	03/07/2016	7.05	2	Jul 2016	3.50
					3	03/07/2016	12/08/2016	28.2	12	Aug 2016	5.20
					4	12/08/2016	11/10/2016	42.3	14	Sep 2016	4.67
					4	12/08/2016	11/10/2016	42.3	14	Oct 2016	4.00
					5	11/10/2016	10/12/2016	42.3	10	Nov 2016	3.33
					5	11/10/2016	10/12/2016	42.3	10	Dec 2016	2.40
					6	10/12/2016	09/01/2017	21.15	2	Jan 2017	0.67

PACKAGING_WORKLOAD_DTL:

LOAD *,

date(PHASE_END,'DD/MM/YYYY') - date(PHASE_START,'DD/MM/YYYY') as Days,

MonthName(date(PHASE_END,'DD/MM/YYYY')) as EndMonthYear,

MonthName(date(PHASE_START,'DD/MM/YYYY')) as StartMonthYear;

LOAD "ACTUAL_DURATION",

"CREATE_DATE",

"DEVELOPER" as [DEVELOPER_DTL] ,

trim(date(date(END_DATE,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD-MMM-YYYY') ) AS [END_DATE_DTL],

"FISCAL_YEAR",

"NUMBER_OF_DAYS",

date(PHASE_END,'DD/MM/YYYY') AS [PHASE_END],

Month(date(PHASE_END,'DD/MM/YYYY')) AS PHASE_END_MONTH,

Year(date(PHASE_END,'DD/MM/YYYY')) AS PHASE_END_YEAR,

"PHASE_ID",

date(PHASE_START,'DD/MM/YYYY') AS [PHASE_START],

//date(PHASE_START,'DD/MM/YYYY') AS [PHASE_START_MR],

Month(date(PHASE_START,'DD/MM/YYYY')) AS PHASE_START_MONTH,

Year(date(PHASE_START,'DD/MM/YYYY')) AS PHASE_START_YEAR,

"PPJ_ID",

"RATING" as [RATING_DTL],

trim(date(date(SHIP_DATE,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD-MMM-YYYY') ) AS [SHIP_DATE_DTL],

date(PHASE_START,'DD/MM/YYYY') AS [START_DATE_DTL],

"UNIT_IN_PHASE",

"UNITS_IN_PHASE_PER_WEEK",

"UNITS_OF_WORK" as [UNITS_OF_WORK_DTL],

(Year([PHASE_END])*12 + Month([PHASE_END]))-(Year([PHASE_START])*12 + Month([PHASE_START])) AS MONTHS_IN_PHASE

FROM

Z:\SCI\GSC\C2M\DEV\Packaging\Management Reports\QVD Generator\PACKAGING_WORKLOAD_DTL_QVD_GENERATOR_DEV.qvd (qvd)

Where FISCAL_YEAR >='FY12' and FISCAL_YEAR < 'FY20';

FinalTable:

LOAD PPJ_ID,

PHASE_ID,

MonthName(AddMonths(StartMonthYear, IterNo() -1 )) as MonthYear

Resident PACKAGING_WORKLOAD_DTL

While AddMonths(StartMonthYear, IterNo() -1 ) <= EndMonthYear;

Re: Correct MONTHYEAR combination not showing using IterNo

Anil_Babu_Samineni — Tue, 09 Aug 2016 16:02:26 GMT

Can I know what error you are getting?

You already arrange the fitter the date of format. For month and year no need to add format again

Re: Correct MONTHYEAR combination not showing using IterNo

mrainford — Tue, 09 Aug 2016 16:30:02 GMT

It's not an error - however I am not getting the results I need as the IterNo() logic is not giving me the correct range of MONTH_YEAR between PHASE_START and PHASE_END.

These are the results I am looking for. What am I doing wrong in the load script?

PHASE_ID	PHASE_START	PHASE_END	MONTH_YEAR
1	24/06/2016	24/06/2016	Jun-16
2	24/06/2016	3/7/2016	Jun-16
			Jul-16
3	3/7/2016	12/8/2016	Jul-16
			Aug-16
4	12/8/2016	11/10/2016	Aug-16
			Sep-16
			Oct-16
5	11/10/2016	10/12/2016	Oct-16
			Nov-16
			Dec-16
6	10/12/2016	9/1/2017	Dec-16
			Jan-17

Your assistance is much appreciated.

Re: Correct MONTHYEAR combination not showing using IterNo

MarcoWedel — Tue, 09 Aug 2016 22:34:57 GMT

please post some sample qvd

thanks

regards

Marco

topic Re: Correct MONTHYEAR combination not showing using IterNo in QlikView

Correct MONTHYEAR combination not showing using IterNo

Re: Correct MONTHYEAR combination not showing using IterNo

Re: Correct MONTHYEAR combination not showing using IterNo

Re: Correct MONTHYEAR combination not showing using IterNo