4 Replies Latest reply: Jun 17, 2015 7:26 AM by Yeo Poh sai RSS

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

    Yeo Poh sai

      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 ?



        • Re: My year field not work when i select SOURC=TH Hi
          Yeo Poh sai

          Hi All


          My QV Doc



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

              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.



              • Re: My year field not work when i select SOURC=TH Hi
                jagan mohan rao appala



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




                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;