Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
katetsan
Creator
Creator

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
tresesco
MVP
MVP

Have you tried with where clause ? like:

Load

          *

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

katetsan
Creator
Creator
Author

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'

tresesco
MVP
MVP

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
Creator III
Creator III

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
Creator
Creator
Author

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!

tresesco
MVP
MVP

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

katetsan
Creator
Creator
Author

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