Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Hi everyone

I got the the following select in my script:

Load

   OBJNR,

   STAT,

   INACT,

   CHGNR;

SQL Select OBJNR STAT INACT CHGNR  from JEST where right(OBJNR,7) > 1031000 ;

The field OBJNR is like "OR0000001033466", and i just need to get where the last 7 numbers are > '1030000'.

I want to make a where right(OBJNR,7) > '1030000' in the where clause. but does not work.

I also try: where num(right(OBJNR,7)) >  '1030000'


but doesn't work too.

can anyone help me?

Tags (4)
1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Combining responses from Clever Anjos and Angad Singh, and adding from myself just a little:

Load

   OBJNR,

   STAT,

   INACT,

   CHGNR

where right(OBJNR,7) > 1031000;

SQL Select OBJNR, STAT, INACT, CHGNR  from JEST;

7 Replies
Employee
Employee

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

SQL queries does not support QlikView Functions, they´re handled by the Database

If you using Oracle for example try to convert to a number using Oracle Functions

where to_number(right(OBJNR,7)) > 1030000

Not applicable

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

The statement you have written:

SQL Select OBJNR STAT INACT CHGNR  from JEST where right(OBJNR,7) > 1031000 ;


don't you think you need commas between each field and also try using '1031000'.


Are you getting any error, it is just returning all records?

mov
Esteemed Contributor III

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Combining responses from Clever Anjos and Angad Singh, and adding from myself just a little:

Load

   OBJNR,

   STAT,

   INACT,

   CHGNR

where right(OBJNR,7) > 1031000;

SQL Select OBJNR, STAT, INACT, CHGNR  from JEST;

jpapador
Valued Contributor

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

I agree with Michael, though I have noticed that most data fields are stored as text in SAP you may need to use a num wrapper to format the field as such, for example:

Load

   OBJNR,

   STAT,

   INACT,

   CHGNR

where right(Num(OBJNR),7) > 1031000;

SQL Select OBJNR, STAT, INACT, CHGNR  from JEST;

Employee
Employee

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Michael Solomovich, I´m your fan and I´d like to ask about one doubt about this type of code (Where condition at LOAD, not SQL).

In this case, all lines are retrievied by database and QlikView filters? I think this could be unnapropriate for example if database retrives millions of rows if QV need thousands or even less.

mov
Esteemed Contributor III

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Clever Anjos - a valid doubt.

Yes, if we filter on LOAD instead of SQL, all records are retrieved from the data source and filtered after that.  Hence it is slower.  By using filtering on LOAD, we're gaining clarity and flexibility of the script (at least for me), but loosing load speed.  So, it depends on priorities.

If the number of rows is large, and we need only a small fraction of it, and reload time is a priority, it is better to filter on the SQL SELECT rather than on LOAD.

Million rows take seconds to load, so the delay is insignificant even if you need just a few records.  When there are hundreds of millions, and we need a small portion, then filtering on SQL SELECT makes noticeable difference.

Thanks for pointing it out - it may be essential when choosing how to filter.

Regards,

Michael

Employee
Employee

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Thank you for sharing your wisdom Michael Solomovich

Community Browser