topic Re: Urgent (Issue) in QlikView

Urgent (Issue)

Tue, 26 Aug 2014 11:26:43 GMT

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

Re: Urgent (Issue)

sundarakumar — Tue, 26 Aug 2014 11:33:18 GMT

have u replaced the QVD's also? what is the frequency of refresh?

-Sundar

Re: Urgent (Issue)

Tue, 26 Aug 2014 11:40:43 GMT

yes ,same

i replaced everything

Re: Urgent (Issue)

vinay_hg — Tue, 26 Aug 2014 11:41:23 GMT

just cross verify the logic date filters you have used. i mean 2012-2013 or 2013-2014. if this is correct then check the date filed which you are using to arrive this financial year format. according to me in the db the date might got updated with wrong date (data) so urs value may differing in case if u used proper date formatting. u need to check in oracle db for data is there any change in date format or date value.

Re: Urgent (Issue)

Tue, 26 Aug 2014 11:44:55 GMT

how i can verify whether they changed the date format

Do ,the change n the date format effect the financial year

Re: Urgent (Issue)

vinay_hg — Tue, 26 Aug 2014 11:49:49 GMT

check in the extract or while u created raw qvd, which format date is coming. yes this will effect if incase as u tols while mirroring all 2014 dates if got updated to 2013 or 2012 due to some issue as u mentioned earlier sap team faced some issue. secondly if your date reading format is wrong then also possible.

tell me 2012-2013 means from jan2012 to dec2012 or apr2012 to mar2013?

Re: Urgent (Issue)

Tue, 26 Aug 2014 11:50:14 GMT

they don't want to give rights in oredr to directly access SAP

Re: Urgent (Issue)

senpradip007 — Tue, 26 Aug 2014 11:50:16 GMT

Why do you not use SAP Connector to extract data from SAP instead of Oracle? QlikView can extract data from SAP quite effectively.

Re: Urgent (Issue)

Tue, 26 Aug 2014 11:51:28 GMT

security reason

Re: Urgent (Issue)

sundarakumar — Tue, 26 Aug 2014 11:58:13 GMT

reload freuency pls. bec the issue is with data not with Qlikview. if the relaod frequency is 30 mins then ur replaecement would be a gone within next half an hour.

-Sundar

Re: Urgent (Issue)

vinay_hg — Tue, 26 Aug 2014 12:02:15 GMT

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

Re: Urgent (Issue)

Tue, 26 Aug 2014 12:09:42 GMT

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

Re: Urgent (Issue)

vinay_hg — Tue, 26 Aug 2014 12:14:39 GMT

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?

Re: Urgent (Issue)

Tue, 26 Aug 2014 12:18:30 GMT

apr2012 to mar2013?

Re: Urgent (Issue)

vinay_hg — Tue, 26 Aug 2014 12:21:34 GMT

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?

Re: Urgent (Issue)

Wed, 27 Aug 2014 04:38:15 GMT

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

];

Re: Urgent (Issue)

Wed, 27 Aug 2014 04:56:30 GMT

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

];

Re: Urgent (Issue)

Wed, 27 Aug 2014 05:04:28 GMT

how to do that

Re: Urgent (Issue)

Wed, 27 Aug 2014 06:23:36 GMT

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

Re: Urgent (Issue)

vinay_hg — Tue, 02 Sep 2014 06:32:28 GMT

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.