Urgent (Issue)

[#1]

I have one application named as finance it fetch the data from Oracle .

there data comes from SAP then dump into Oracle, last week while dumping from SAP into Oracle ,their SAP team face some issue called mirroring( i dn't know ,what it is & its consequences)

Now QLikview  showing incorrect data , but when check in the backup data is correct ,

Once ,I replace the main folder app with backup and qvd generator with backup qvd generator  ,qlikview  populate incorrect data

Data of 2013-2014 displayed in  2012-2013

Any idea ,how to resolve this prtoblem

[vinay_hg]

dont check in sap, check in qlikview when u connect oracle and created first raw qvd file from db.

[#3]

As per my user ,it is not possible that date format has been changed either in Oracle / SAP

As it is already fixed.

Is it so

[vinay_hg]

thats y check date range in qlikview wat it is populating. and also just tell me 2012-2013 means from jan2012 to dec2012 or apr2012 to mar2013?

[#5]

apr2012 to mar2013?

[vinay_hg]

is it one date u are using while creting fiscal? or combinations of 2 or more date columns?

eg:

start date, end date, doj, date creation

r u using only 1 date like date creation for calender or start date+end date+date creation in fiscal?

[#7]

FiscalCalendar:

LOAD DATE(TempDate,'DD-MM-YYYY') as  date_posting,

//     DATE#(TempDate,'DD-MM-YYYY') as  date,

     Year(YearStart(TempDate,0,4)) as  Year,

     Year(YearStart(TempDate,0,4))& IF(Num(Month(TempDate))=4,1,

      IF(Num(Month(TempDate))=5,2,

      IF(Num(Month(TempDate))=6,3,

      IF(Num(Month(TempDate))=7,4,

      IF(Num(Month(TempDate))=8,5,

      IF(Num(Month(TempDate))=9,6,

      IF(Num(Month(TempDate))=10,7,

      IF(Num(Month(TempDate))=11,8,

      IF(Num(Month(TempDate))=12,9,

      IF(Num(Month(TempDate))=1,10,

      IF(Num(Month(TempDate))=2,11,

      IF(Num(Month(TempDate))=3,12)))))))))))) as  P_YEAR,

     Capitalize(MONTHName( TempDate)) AS MonthName,

     num(MONTHName( TempDate)) as MonthNameNum,

      Capitalize(MONTH( TempDate)) AS Month,

     YearName( TempDate,0,4) As FiscalYear,

     YearName( TempDate,-1,4) As LastFiscalYear,

     num(YearName( TempDate,0,4)) As FYN,

     num(YearName( TempDate,0,4)) As FiscalYearNum,

     YearName( TempDate) As YearName,

      Day( TempDate) As Day,

      IF(Num(Month(TempDate))=4,1,

      IF(Num(Month(TempDate))=5,2,

      IF(Num(Month(TempDate))=6,3,

      IF(Num(Month(TempDate))=7,4,

      IF(Num(Month(TempDate))=8,5,

      IF(Num(Month(TempDate))=9,6,

      IF(Num(Month(TempDate))=10,7,

      IF(Num(Month(TempDate))=11,8,

      IF(Num(Month(TempDate))=12,9,

      IF(Num(Month(TempDate))=1,10,

      IF(Num(Month(TempDate))=2,11,

      IF(Num(Month(TempDate))=3,12)))))))))))) AS Period_1

Resident TempCalendar;

DROP Table TempCalendar;

load * inline

[

   Period_1,FiscalMonth,CalMonth,FiscalQuarter,CalQuater

    1,Apr,Jan,Q1,Q2

    2,May,Feb,Q1,Q2

    3,Jun,Mar,Q1 ,Q2

    4,Jul,Apr,Q2,Q3

    5,Aug,May,Q2,Q3

    6,Sep,Jun,Q2,Q3

    7,Oct,Jul,Q3,Q4

    8,Nov,Aug,Q3,Q4

    9,Dec,Sep,Q3,Q4

    10,Jan,Oct,Q4,Q1

    11,Feb,Nov,Q4,Q1

    12,Mar,Dec,Q4,Q1

];

[#8]

LET varMinDate = Num(Date#('20100101','YYYYMMDD'));

LET vToday = num(today());

LET VAR=(yearname(Today(),0,4));

TempCalendar:

load

$(varMinDate) + IterNo()-1 as NUM,

DATE($(varMinDate) +IterNo()-1) as  TempDate

AUTOGENERATE 1 WHILE $(varMinDate) + IterNo()-1 <=

$(vToday);

FiscalCalendar:

LOAD DATE(TempDate,'DD-MM-YYYY') as  date_posting,

//     DATE#(TempDate,'DD-MM-YYYY') as  date,

     Year(YearStart(TempDate,0,4)) as  Year,

     Year(YearStart(TempDate,0,4))& IF(Num(Month(TempDate))=4,1,

      IF(Num(Month(TempDate))=5,2,

      IF(Num(Month(TempDate))=6,3,

      IF(Num(Month(TempDate))=7,4,

      IF(Num(Month(TempDate))=8,5,

      IF(Num(Month(TempDate))=9,6,

      IF(Num(Month(TempDate))=10,7,

      IF(Num(Month(TempDate))=11,8,

      IF(Num(Month(TempDate))=12,9,

      IF(Num(Month(TempDate))=1,10,

      IF(Num(Month(TempDate))=2,11,

      IF(Num(Month(TempDate))=3,12)))))))))))) as  P_YEAR,

     Capitalize(MONTHName( TempDate)) AS MonthName,

     num(MONTHName( TempDate)) as MonthNameNum,

      Capitalize(MONTH( TempDate)) AS Month,

     YearName( TempDate,0,4) As FiscalYear,

     YearName( TempDate,-1,4) As LastFiscalYear,

     num(YearName( TempDate,0,4)) As FYN,

     num(YearName( TempDate,0,4)) As FiscalYearNum,

     YearName( TempDate) As YearName,

      Day( TempDate) As Day,

      IF(Num(Month(TempDate))=4,1,

      IF(Num(Month(TempDate))=5,2,

      IF(Num(Month(TempDate))=6,3,

      IF(Num(Month(TempDate))=7,4,

      IF(Num(Month(TempDate))=8,5,

      IF(Num(Month(TempDate))=9,6,

      IF(Num(Month(TempDate))=10,7,

      IF(Num(Month(TempDate))=11,8,

      IF(Num(Month(TempDate))=12,9,

      IF(Num(Month(TempDate))=1,10,

      IF(Num(Month(TempDate))=2,11,

      IF(Num(Month(TempDate))=3,12)))))))))))) AS Period_1

Resident TempCalendar;

DROP Table TempCalendar;

load * inline

[

   Period_1,FiscalMonth,CalMonth,FiscalQuarter,CalQuater

    1,Apr,Jan,Q1,Q2

    2,May,Feb,Q1,Q2

    3,Jun,Mar,Q1 ,Q2

    4,Jul,Apr,Q2,Q3

    5,Aug,May,Q2,Q3

    6,Sep,Jun,Q2,Q3

    7,Oct,Jul,Q3,Q4

    8,Nov,Aug,Q3,Q4

    9,Dec,Sep,Q3,Q4

    10,Jan,Oct,Q4,Q1

    11,Feb,Nov,Q4,Q1

    12,Mar,Dec,Q4,Q1

];

[#9]

how to do that

[#10]

Thanks vinay , ur suggestion helps me alot

As per my observation, in their data base,they have two entry  year i.e.GJAHR and BUDAT i.e. Date  which is format of YYYYMMDD

we have created financial year in Qlikview,thier financial year is from apr 2013 -mar2014

GJAHR  ,BUDAT

2013  20140103

2013  20140203

2013  20140303

2013   20130412

2013   20130906

2013   20131114

According  in first 3 rows value of GJAHR should be 2014 as these are the year of jan ,feb and mar month

Please tell me  whether i am correct or not

[vinay_hg]

Yes u are right.. as i mentioned any issue in the data please first check date formats in source data, in qlikview model and in calender. and i suggest to use calender built in qvs file for creating calender instead of using the scrit you had written.