topic Re: Values not showing correctly in QlikView

Values not showing correctly

deepakqlikview_123 — Wed, 21 May 2014 07:12:29 GMT

Hi All,

Please find attached Qvw file.

For showing month,quarter,year,Day properly I have created new calendar table using resident data(On retail table)

I have left joined calendar with organizationmaster,but it seems results are not displaying correctly.

Please suggest.

Thanks

Re: Values not showing correctly

Wed, 21 May 2014 08:01:37 GMT

First you are matching the Calendar by DealerID as KeyField - to create an Calendar you should use the DATEFIELD as KeyField,

Second I think you having dates in your source data which aren't format correct try to use date#() instead of date() to cast a right formated date by Loading it.

Somthing like this:

Dealer1:

LOAD ......

date#(DealerInvoiceDate,'DD-MM-YYYY') as DealerInvoiceDateKey

.......;

Tmp_MinMaxDate:

Replace Load

Min(%DealerInvoiceDateKey) As MinDate,

Max(%DealerInvoiceDateKey) As MaxDate

Resident Dealer1;

/******************** Set Vars *******************/

Let vMinDate = Peek('MinDate', 0, 'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDate');

Drop Table Tmp_MinMaxDate;

Datefield:

Replace Load date($(vMinDate)+Iterno()-1) As Datefield

Autogenerate 1

While $(vMinDate)+Iterno()-1 <= $(vMaxDate);

Calendar:

Replace Load *,

Datefield As %DealerInvoiceDateKey,

Datefield As MyDate,

Year(Datefield) As Year,

Year2date(Datefield)*-1 As YTDcurYear,

Year2date(Datefield,-1)*-1 As YTDlastYear,

Month(Datefield) As Monat,

date(monthstart(Datefield), 'MMM-YYYY') As curMonth,

'Q' & Ceil(Month(Datefield) / 3) As Quarter,

week(Datefield) As Week,

week(Datefield) & '-' & Year(Datefield) As curWeek,

Day(Datefield) As Day,

weekDay(Datefield) As WeekDay

Resident Datefield

Order by Datefield Asc;

Drop Table Datefield;

Re: Values not showing correctly

deepakqlikview_123 — Wed, 21 May 2014 08:19:12 GMT

Hi Thoms,

Can you please tell me how there are 2 calendar table will works.

Thanks

Re: Values not showing correctly

Wed, 21 May 2014 08:58:35 GMT

Sorry was an mistake I have correct my last post was the Rest of my first correction - forgot to delete

Re: Values not showing correctly

deepakqlikview_123 — Wed, 21 May 2014 11:20:52 GMT

Hi Thomas,

Can you please suggest on this.

Thanks,

Re: Values not showing correctly

ramasaisaksoft — Wed, 21 May 2014 12:34:13 GMT

Hi Deepak ,

can u please check/Modify u r code like below for Date purpose.

//Shows Data for Yearwise

Year("Quote Date") as Year_QU,

//Shows Data for Month

Month("Quote Date") as Month_QU,//

//Shows Data for Day

Day("Quote Date") as Day_QU,

//Shows Data for Quarter

If(Num(Month([Quote Date]))=1,01,If(Num(Month([Quote Date]))=2,02,If(Num(Month([Quote Date]))=3,03,

If(Num(Month([Quote Date]))=4,04,If(Num(Month([Quote Date]))=5,05,If(Num(Month([Quote Date]))=6,06,

If(Num(Month([Quote Date]))=7,07,If(Num(Month([Quote Date]))=8,08,If(Num(Month([Quote Date]))=9,09,

If(Num(Month([Quote Date]))=10,10,If(Num(Month([Quote Date]))=11,11,If(Num(Month([Quote Date]))=12,12)))))))))))) as Month_Nbr_QU,

If(Num(Month([Quote Date]))=1 or Num(Month([Quote Date]))=2 or Num(Month([Quote Date]))=3,'Q1',

If(Num(Month([Quote Date]))=4 or Num(Month([Quote Date]))=5 or Num(Month([Quote Date]))=6 ,'Q2',

If(Num(Month([Quote Date]))=7 or Num(Month([Quote Date]))=8 or Num(Month([Quote Date]))=9 ,'Q3',

If(Num(Month([Quote Date]))=10 or Num(Month([Quote Date]))=11 or Num(Month([Quote Date]))=12 ,'Q4')))) as Quarter_QU,

i hope it will help u.