My year field not work when i select SOURC=TH Hi

[paulyeo11]

All I have read the all country data , all data able to load into QV fine. except for SOURC=TH , have issue. when i check for SOURC=TH , i do have date script :-

year(@93:104T) as [year_NF], @93:104T as [date],

I notice that , it does generate the date field and it is okay. I have a time line script that , recode the date field to year field :-

TimeLine: LOAD DISTINCT *,

year([date]) as [year],  // Org code Now my issue is SOURC=TH , does not able to generate the year field.

Hope some one can help me how to check for the error ?

Paul

[paulyeo11]

Hi All

My QV Doc

Paul

[#3]

Hi Paul,

I guess there are some issues with data model.Try to get rid of the synthetic keys and please check the attached qvw for reference.

Regards,

Jemimah

[jagan · Accepted solution]

Hi,

Change this in your script, comment the bolded lines in your script and try reloading, the issue is because of Synthetic key it seems

TimeLine:

LOAD DISTINCT *,

//date AS date,

// num(month([date])) as [monthnum],

  num(month([date])) as [monthnum_t],

  year([date]) as [year],  // Org code

  year([date]) as [Year],  // Org code

  Right(year(date),2) as YEAR_,   // use right

  date(date,'YY') as year2,   // use date format can sort but 4 digit.

// Date(date#(date,'DD/MM/YYYY'),'YY') as YEAR1,   // use date format can sort 2 digit but with duplicate year.

// num(right(Year(date#(date,'DD/MM/YYYY')), 2)) as Year2Digits,

num(right(Year(date#(date,'DD/MM/YYYY')), 2),'00') as Year2Digits,

// right(Year(date#(date,'DD/MM/YYYY')), 2) as Year2Digits,

  Year(date#(date,'DD/MM/YYYY')) as YEAR1,

  'Q' & ceil(Month([date]),3)/3 as [quarter],

  'Q' & ceil(Month([date]),3)/3 as [Quarter],

  num(month([date])) as [month],

  num(month([date])) as [Month],

    //year(today()) - year([date])) * 12 + month(today()) - month([date]) + 1 as Month_n,

// year(today()) - year([date]) + 1                                         as Year_n,

  day([date]) as [day],

  Date(Monthstart([date]), 'YY MMM') as YearMonth,

  dual(week([date])&'-'&weekyear([date]),weekstart([date])) as YearWeek,

  Date(Monthstart([date]), 'DD-MMM-YYYY') as YearMonthDay,

  Week([date]) as Week,

  text(month([date])) & '-week' & text(week([date]) - week(monthstart([date])) + 1) as Month_Week,

  Right(year(date),2) & '-Q' & ceil(Month(date)/3)             as [YearQtr],

  year([date]) & '-H' & ceil(Month([date])/6) as [YearHalf], 

  InQuarterToDate(date, today(), 0) * -1  as QTD_Flag,

  InQuarterToDate(date, today(), -1) * -1 as LQTD_Flag,

  InQuarterToDate(date, today(), -4) * -1 as LYQTD_Flag,

  if(Year(date) & '-' & Month(date) =Year(Today()) & '-' & Month(Today()), 'Y', 'N') as THISMONTH_YN,

  if(Year(date) & '-' & Month(date) =Year(Today()) & '-' & Month(Today()),1,0) as MTD,

  if(Year(date) & '-' & Month(date) =Year(Today()) & '-' & Month(Today()),1,0) as PMTD,

  Year2Date(date) * -1     as YTD_Flag,

  Year2Date(date, -1) * -1   as LY_YTD_Flag,

// InMonthToDate(date) * -1     as MTD_Flag,

//If(MonthYear(today())=MonthYear(date),1,0) as MTD.If(MonthYear(today())=MonthYear(date),1,0) as MTD.

  inyeartodate(date, $(vToday), 0) * -1 AS CYTD, //Current year-to-date

  inyeartodate(date, $(vToday), -1) * -1 AS FPYTD, //First prior year-to-date

  // YTD month closed.

  If(Num([date]) >= $(vYearStart) and Num([date]) < $(vMonthNow), -1, 0) As YTD,

  If(Num([date]) >= $(vYearStartLY) and Num([date]) < $(vMonthNowLY), -1, 0) As LY_YTD;

LOAD Distinct   [date] resident sales;

[paulyeo11]

Hi Mohan

Thank you

Paul