Data limitation in qliksense

[QS21]

Hi everyone,

I am trying to limit data in the data load editor. I have put the below "where" condition at the end of my load script

Tried various but it fails saying "certain field is not found in the table". If I comment that field and run again it says another field is not found. If I dont use where condition, my script is running successfully. Below are what I have tried:

FROM `TEST_TABLE`.`KPI_TEST`
where date(LOAD_TIME) >= add_months(today(),-13);

where date(LOAD_TIME)>= date(addmonths(Today(),-2),'DD/MM/YYYY');

where date(LOAD_TIME)>= date(MonthStart(today(),-2));

Please help!

[rwunderlich]

Can post your complete Load statement?

-Rob

[sidhiq91]

@QS21 as @rwunderlich  mentioned could you please post the complete script, it will help us to resolve the issue. Thanks.

[QS21]

LIB CONNECT TO 'Google_BigQuery_******;

TEST_TABLE:

LOAD

R_ID,  A_ID, I_ID, D_ID, DATE_TIME, NAME;

SELECT

R_ID,  A_ID, I_ID, D_ID, DATE_TIME, NAME

FROM `TEST_TABLE`.`KPI_TEST`
where date(LOAD_TIME) >= add_months(today(),-13);

where date(LOAD_TIME)>= date(addmonths(Today(),-2),'DD/MM/YYYY');

where date(LOAD_TIME)>= date(MonthStart(today(),-2));

[sidhiq91]

@QS21  you are getting the error because the where clause field should be a part of your load as well. Please add LOAD_TIME  and try to load.

[OmarBenSalem · Accepted solution]

When you use the where condition in the select part, you should use sql functions (depends on your database)

If you want to use Qlik functions, you put your where in the load part:

load ID where condition with qlik functions;

select BLABLA from Source where condition with sql functions; 

[rwunderlich · Accepted solution]

Since you are using Qlik functions (AddMonths) the where clause should be in your Qlik Load like this:

LIB CONNECT TO 'Google_BigQuery_******;

TEST_TABLE:
LOAD
R_ID,  A_ID, I_ID, D_ID, DATE_TIME, NAME
where date(LOAD_TIME) >= add_months(today(),-13)
and date(LOAD_TIME)>= addmonths(Today(),-2)
and date(LOAD_TIME)>= MonthStart(today(),-2)
;

SELECT
R_ID,  A_ID, I_ID, D_ID, DATE_TIME, NAME, LOAD_TIME
FROM `TEST_TABLE`.`KPI_TEST`

I'm assuming "LOAD_TIME" is a valid field in your TEST_TABLE. Note I added it to the SELECT so it will be available to the LOAD.

-Rob

[QS21]

Thank you, it worked!