topic Re: How to write WHERE clause in QlikView

How to write WHERE clause

Tue, 30 Jul 2013 20:21:48 GMT

Hi,

Could anyone help me in this.

I have a column Queue with data quality,regionalexception,divisionreveiew,nationdept,sales and another column Department with data hr,manager,accounts,ceo.

i need to consider both columns Queue and Department having regionalexception,divisionreveiew in Queue and hr,manager in Department.

Could anyone help me how to write WHERE clause.

Thanks.

Re: How to write WHERE clause

MayilVahanan — Tue, 30 Jul 2013 20:32:38 GMT

Try like this

Load * from tablename

Where wildmatch(Queue,'regionalexception','divisionreveiew') and Wildmatch(Department,'hr','manager');

hope it helps

Re: How to write WHERE clause

Tue, 30 Jul 2013 20:41:41 GMT

Hi,

Thanks for your answer it helped me and I have column When which contains date. Could you please help me how to create calendar by using this field?

Thanks

Re: How to write WHERE clause

MayilVahanan — Tue, 30 Jul 2013 21:28:33 GMT

Load

Year(DateField) as Year,

Month(DateField) as Month,

Week(DateField) as Week,

Day(DateField) as Day,

'Q'&Ceil(Month(DateField)/3) as Quarter,

Date(DateField) as Date

From Tablename;

Here DateField is When

Re: How to write WHERE clause

Tue, 30 Jul 2013 21:44:27 GMT

Thanks for your answer and Could I know Do I need to create Master Calendar? That means how can I get Month JAN-DEC and Year as 2013-2014,2014-2015,2015-2016 and so on and Quarter as Q1,Q2,Q3,Q4

Thanks.

Re: How to write WHERE clause

MayilVahanan — Tue, 30 Jul 2013 22:20:52 GMT

//Get Minimum date from table

TempDateMin:

LOAD

Min(DateNum) AS MinDateNum

RESIDENT TableName;

//Get and store Minimum devlivery date

LET vDateMin = FieldValue('MinDateNum', 1);

LET vDateMax = Floor(Num(Today()));

DROP TABLE TempDateMin;

MasterCalendar:

LOAD

DateNum,

Year(DateNum) AS "Calendar Year",

Month(DateNum) & '-' & DATE(DateNum,'YY') as "Calendar Month Year",

Month(DateNum) as "Calendar Month",

Day(DateNum) * 1 as "Calendar Day",

Week(DateNum) * 1 as "Calendar Week",

WeekDay(DateNum) * 1 as "CalendarWeekDay",

Round(DateNum) as DateLink,

'Q' & Ceil(Month(DateNum) / 3) AS "Calendar Quarter";

LOAD

($(vDateMin) + (RecNo()-1)) AS DateNum

AUTOGENERATE ($(vDateMax ) - $(vDateMin) + 1);

Hope it helps

Re: How to write WHERE clause

MayilVahanan — Tue, 30 Jul 2013 22:21:35 GMT

For master calendar,

Please refer

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

Hope you can understand it