Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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>
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
Have you tried with where clause ? like:
Load
*
From <> where YearMonth='2017/07' and Company=11;
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'
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.
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
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!
Is the script running without the where clause? Could you post the error screen shot?
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