topic Re: Reload data with condition in QlikView

Reload data with condition

katetsan — Mon, 20 Nov 2017 06:43:46 GMT

Hi Communities,

I'm trying to load data from table in database under conditional fields.

For example, I have a data set with multiple fields as below and I would like to load data with specific conditions, like

1) Month =2017/07 2) Company Name = 11 .... etc

YearMonth	Company	Factory ID	Customers ID
2017/01	12	11	111
2017/02	13	12	112
2017/07	11	11	113
2017/07	13	12	114
2017/10	11	13	115
2017/09	12	14	116

Thanks,

Kate

Re: Reload data with condition

tresB — Mon, 20 Nov 2017 06:55:20 GMT

Have you tried with where clause ? like:

Load

From <> where YearMonth='2017/07' and Company=11;

Re: Reload data with condition

katetsan — Mon, 20 Nov 2017 07:05:04 GMT

Hi,

Actually the format of field YearMonth is like 2017/01/01, and I did tried where clause with Year() function.

But It shows there's no Year function.

*** Scripts***

Load *,

Select *,

From <> where Year(YearMonth)=2017 and Month(YearMonth)='Jul'

Re: Reload data with condition

tresB — Mon, 20 Nov 2017 07:11:04 GMT

That means your DB doesn't support year(). You can find for a similar function for your db or use year() in load statement that would get executed by qv. try like:

Load

Where year(...)....;

Sql Select * from <>;

This way, the db will send all the data to qv and qv will filter accordingly before it gets loaded. If you want this filter to be applied at the db itself (which is generally better option), you have to find the functions that are supported with your db and apply where clause accordingly.

Re: Reload data with condition

woshua5550 — Mon, 20 Nov 2017 07:13:40 GMT

Load *

where Year(YearMonth)=2017 and Month(YearMonth)='Jul';

Select *

From <your table>

if the format of field YearMonth is like 2017/01/01, you need

SET DateFormat='YYYY/MM/DD';

before the script

Re: Reload data with condition

katetsan — Mon, 20 Nov 2017 08:14:44 GMT

Hi Tresesco,

The db that I'm connecting is SAP. I've searched on website, and it seems the year() works on SAP.

Or did I mistake the information?

Thanks a lot!

Re: Reload data with condition

tresB — Mon, 20 Nov 2017 08:43:27 GMT

Is the script running without the where clause? Could you post the error screen shot?

Re: Reload data with condition

katetsan — Tue, 21 Nov 2017 02:46:53 GMT

Hi Tresesco,

Very appreciate your kindly support and instruction. I tried the script as below, and it works.

***** Script *****

Load*;

Select * from Table where (YearMonth>= '20170701' and YearMonth <= '20170731');

....

Kate