Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

katetsan
New Contributor II

Reload data with condition

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

<Data Set Table>

YearMonthCompanyFactory IDCustomers ID
2017/011211111
2017/021312112
2017/071111

113

2017/071312114
2017/101113115
2017/091214116

Thanks,

Kate

7 Replies
MVP
MVP

Re: Reload data with condition

Have you tried with where clause ? like:

Load

          *

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

katetsan
New Contributor II

Re: Reload data with condition

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'

MVP
MVP

Re: Reload data with condition

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.

woshua5550
Contributor III

Re: Reload data with condition

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

katetsan
New Contributor II

Re: Reload data with condition

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!

MVP
MVP

Re: Reload data with condition

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

katetsan
New Contributor II

Re: Reload data with condition

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

Community Browser