Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi All
My QV Doc
Paul
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
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;
Hi Mohan
Thank you
Paul