topic How to load data for only Year 2011 in QlikView

How to load data for only Year 2011

Fri, 26 Aug 2011 07:01:21 GMT

Hello Guys:

Could someone please advise me how to limit the load data into Qlikview?

For Example: I would only like to load all the data for Year 2011 and not those data that as from Year 2007 to 2011.

The database starts from 2007 to 2011.

Thank you guys.

How to load data for only Year 2011

Fri, 26 Aug 2011 07:22:48 GMT

You can limit your load with

select column

from table

where year = 2011

or similar in your load statement

How to load data for only Year 2011

its_anandrjs — Fri, 26 Aug 2011 07:42:20 GMT

Hi,

You can write some thing like this

Load

Field1,

Field2,

...

From TableName

Where Year>=2010 and Year<=2011;

Regards

Anand

How to load data for only Year 2011

sushil353 — Fri, 26 Aug 2011 07:49:30 GMT

Hi,

sql select * from table-X where year > 2010 and year <=2011;

How to load data for only Year 2011

Fri, 26 Aug 2011 07:58:55 GMT

Hi Sushil Kumar:

Thanks for your reply.

Do I write this in the Edit script Main Tab or in the first tab?

I have three table for the report so do I need to write all in the different tab under Edit script?

I will just try it.

Thanks for your reply

Re: How to load data for only Year 2011

Miguel_Angel_Baeyens — Fri, 26 Aug 2011 08:01:38 GMT

Hi,

It's all in the script, your table should look like the following

Table: // name of the table in QlikView
LOAD ID, // name of fields in QlikView (you can change them)
     Date,
     Amount;
SQL SELECT ID, Date, Amount
FROM Database.SalesTable
WHERE Year = 2011;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

How to load data for only Year 2011

sushil353 — Fri, 26 Aug 2011 08:11:24 GMT

Hi richardgan,

It hardly matters whether you write in a single tab or in multitab... Multitab is prferred for clear understanding...

HTH

How to load data for only Year 2011

christian77 — Fri, 26 Aug 2011 08:16:00 GMT

Make it dynamic, for further years.

where year = year(today())

How to load data for only Year 2011

Fri, 26 Aug 2011 09:23:18 GMT

Hi Guys:

Thank you all for your kind help.

I try the sugesstion and the error message is:-

Unknown statement Where Year>=2010 and Year<=2011;

The other Error message is:-

Unknown Statement Where Year =2011

Below is the Edit script:

tbinventorytrans:

Load InventoryKey1,

InventoryDate,

Year(InventoryDate) as Inventoryyear1,

Year (InventoryDate) as year, //=====Checking

num(month(InventoryDate)) as month,//==== Checking

num(Month(InventoryDate),'00') as Inventorymonth1,

Year(InventoryDate) & num(Month(InventoryDate)) as InventoryYearMonth1,

Day(InventoryDate) as Inventoryday1,

date(daystart(InventoryDate),'DD-MM-YYYY') as InventoryDate1,

InventoryStatus,

InventoryGoodqty,

InventoryBadqty,

InventoryQcqty,

prodid,

custacc,

invtype,

locid

FROM (qvd);// where $(CurrYr) >=#[Inventoryyear1];

where year >=2011 and year<=2011;

How to load data for only Year 2011

sushil353 — Fri, 26 Aug 2011 09:30:11 GMT

check out which field of your table contains the year information.. then use that field to apply the condition.

How to load data for only Year 2011

kaushiknsolanki — Fri, 26 Aug 2011 09:34:39 GMT

Hi,

Try this,

tbinventorytrans:

Load InventoryKey1,

InventoryDate,

Year(InventoryDate) as Inventoryyear1,

Year (InventoryDate) as year, //=====Checking

num(month(InventoryDate)) as month,//==== Checking

num(Month(InventoryDate),'00') as Inventorymonth1,

Year(InventoryDate) & num(Month(InventoryDate)) as InventoryYearMonth1,

Day(InventoryDate) as Inventoryday1,

date(daystart(InventoryDate),'DD-MM-YYYY') as InventoryDate1,

InventoryStatus,

InventoryGoodqty,

InventoryBadqty,

InventoryQcqty,

prodid,

custacc,

invtype,

locid

FROM (qvd) where year(InventoryDate) = '2011';

Regards,

Kaushik Solanki

How to load data for only Year 2011

Fri, 26 Aug 2011 09:37:27 GMT

Hi,

Check thename of your date field and put that in "WHERE" clause

-Jai

How to load data for only Year 2011

its_anandrjs — Fri, 26 Aug 2011 09:40:26 GMT

Hi,

You need to put some think like this

Load InventoryKey1,

InventoryDate,

Year(InventoryDate) as Inventoryyear1,

Year(InventoryDate) as year, //=====Checking

num(month(InventoryDate)) as month,//==== Checking

num(Month(InventoryDate),'00') as Inventorymonth1,

Year(InventoryDate) & num(Month(InventoryDate)) as InventoryYearMonth1,

Day(InventoryDate) as Inventoryday1,

date(daystart(InventoryDate),'DD-MM-YYYY') as InventoryDate1,

InventoryStatus,

InventoryGoodqty,

InventoryBadqty,

InventoryQcqty,

prodid,

custacc,

invtype,

locid

FROM (qvd)

Where Year(Today()) >=[Inventoryyear1];

Regards,

Anand

Re: How to load data for only Year 2011

christian77 — Fri, 26 Aug 2011 10:02:21 GMT

Hi:

Where Year(Today()) >=[Inventoryyear1]; NO!!!

WHERE year(InventoryDate) = year(today());

1º It must be the original field InventoryDate

2º The field InventoryDate must have a date format in order to extract the year correctly.

3º Even when it should be the same, I place the field first, then equal sign, then the variable, criteria, etc.

Ok.

How to load data for only Year 2011

its_anandrjs — Fri, 26 Aug 2011 10:13:19 GMT

Hi,

You need to put some think like this

Load InventoryKey1,

InventoryDate,

Year(InventoryDate) as Inventoryyear1,

Year(InventoryDate) as year, //=====Checking

num(month(InventoryDate)) as month,//==== Checking

num(Month(InventoryDate),'00') as Inventorymonth1,

Year(InventoryDate) & num(Month(InventoryDate)) as InventoryYearMonth1,

Day(InventoryDate) as Inventoryday1,

date(daystart(InventoryDate),'DD-MM-YYYY') as InventoryDate1,

InventoryStatus,

InventoryGoodqty,

InventoryBadqty,

InventoryQcqty,

prodid,

custacc,

invtype,

locid

FROM (qvd)

Where [Inventoryyear1] >= Year(Today());

It works or not if not let me know

Regards,

Anand

Re: How to load data for only Year 2011

Miguel_Angel_Baeyens — Fri, 26 Aug 2011 10:17:16 GMT

Yep, you need to check your syntax

tbinventorytrans:
Load InventoryKey1,
    InventoryDate,
    Year(InventoryDate) as Inventoryyear1,
    Year (InventoryDate) as year, //=====Checking
    num(month(InventoryDate)) as month,//==== Checking
    num(Month(InventoryDate),'00') as Inventorymonth1,
    Year(InventoryDate) &  num(Month(InventoryDate)) as InventoryYearMonth1,
    Day(InventoryDate) as Inventoryday1,
    date(daystart(InventoryDate),'DD-MM-YYYY') as InventoryDate1,
    InventoryStatus,
    InventoryGoodqty,
    InventoryBadqty,
    InventoryQcqty,
    prodid,
    custacc,
    invtype,
    locid
FROM  (qvd);
where Year(InventoryDate) = Year(Today());

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica