topic Re: Conditional Load in Script in QlikView

Conditional Load in Script

Anonymous — Mon, 24 Nov 2014 06:08:06 GMT

Hi,

I need suggestions from you.

I have a Date, Quarter columns.

Date with 2009 to 2014 Years, and Quarters respective (2009 Q1,Q2,Q3,Q4) to 2014( Q1,Q2,Q3,Q4)

now I need to optimize my application with only latest 10 Quarters i.e (2014 (4QUarters) + 2013(4Quarters) + 2012 (2 Qurters(Q3,Q4)Every time my app should load only latest 10 Quarters of Data.

suggest me on this

Re: Conditional Load in Script

girish_talele — Mon, 24 Nov 2014 06:14:21 GMT

Put Where condition in load statement

e.g.

Load * From xyz.qvd(qvd) Where CALENDAR_DATE >= '01/07/2012';

Regards,

Girish.

Re: Conditional Load in Script

jagan — Mon, 24 Nov 2014 06:19:36 GMT

Hi,

Try like this

TableName:

LOAD

CALENDAR_DATE,

number_format,

QTR_CD,

YY_CD

FROM DataSource

WHERE CALENDAR_DATE >= QuarterStart(Today(), -9);

Hope this helps you.

Regards,

Jagan.

Re: Conditional Load in Script

Anonymous — Mon, 24 Nov 2014 06:26:56 GMT

Jagan,

Thanks for the reply.

It does not yielded results, now even the latest year's data is not showing.

could you help me out this sample app pls

Re: Conditional Load in Script

Anonymous — Mon, 24 Nov 2014 06:33:39 GMT

bump

Re: Conditional Load in Script

Anonymous — Mon, 24 Nov 2014 06:40:25 GMT

Hey Smith,

Small change in Jagan's load script.

TableName:

LOAD

CALENDAR_DATE,

number_format,

QTR_CD,

YY_CD

FROM DataSource

WHERE CALENDAR_DATE >=QuarterStart(AddMonths(AddYears(today(),-2),-3))

BR,

Chinna

Re: Conditional Load in Script

jagan — Mon, 24 Nov 2014 06:41:20 GMT

Hi,

Try this script

Data:

LOAD

Alt(Date(Date#(CALENDAR_DATE, 'D/M/YYYY')), Date(Date#(CALENDAR_DATE, 'DD/MM/YYYY'))) AS CALENDAR_DATE ,

number_format,

QTR_CD,

YY_CD

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE Alt(Date(Date#(CALENDAR_DATE, 'D/M/YYYY')), Date(Date#(CALENDAR_DATE, 'DD/MM/YYYY'))) >= QuarterStart(Today(), -9);

Regards,

jagan.

Re: Conditional Load in Script

anbu1984 — Mon, 24 Nov 2014 06:50:36 GMT

LOAD CALENDAR_DATE,

number_format,

QTR_CD,

YY_CD

FROM

[Query.xlsx]

(ooxml, embedded labels, table is Sheet1) Where Date#(YY_CD,'YYYYMM') >= AddMonths(Today(),-40);

Re: Conditional Load in Script

Anonymous — Mon, 24 Nov 2014 06:53:06 GMT

I am getting 6 Quarters Data only, Could you help me out to get the 10 Quarters?

Thanks for your time and solution

Re: Conditional Load in Script

Anonymous — Mon, 24 Nov 2014 06:55:48 GMT

Hey,

Have you tried this condition in where clause:

WHERE CALENDAR_DATE >=QuarterStart(AddMonths(AddYears(today(),-2),-3))

BR,

Chinna

Re: Conditional Load in Script

anbu1984 — Mon, 24 Nov 2014 07:02:31 GMT

Attached file Query.xlsx has only 6 quarters

Re: Conditional Load in Script

jagan — Mon, 24 Nov 2014 07:08:35 GMT

Hi John,

Your file is having only 6 quarters of data, please verify your file.

Regards,

Jagan.

Re: Conditional Load in Script

daveamz — Mon, 24 Nov 2014 07:45:23 GMT

Hi John,

It is very simple. Last 10 quarters actually means last 30 months. So, you can calculate 30 months back from today, than use this var in WHERE clause.

//Last 10 quarters = last 30 months:

LET vLastQuarters = MonthStart(AddMonths(Today(), -30));

Data:

LOAD CALENDAR_DATE,

Alt(Date(CALENDAR_DATE), Date(Date#(CALENDAR_DATE, 'DD/MM/YYYY'))) as Date, //some values are in DD/MM/YYYY format.

number_format,

QTR_CD,

YY_CD

FROM

Query.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Alt(Date(CALENDAR_DATE), Date(Date#(CALENDAR_DATE, 'DD/MM/YYYY'))) >= $(#vLastQuarters);

Regards,

David