topic Re: left join error syntax related in QlikView

left join error syntax related

Thu, 12 Jun 2014 20:59:51 GMT

hi I have a query like below:

Master:

SQL SELECT

from sometable

where (a='s' or a='p') and year(n) >= year(CURRENT) -2;

Left join (Master)

......

Problem I am facing is that left join does not work it says table 'Master' not found. What I have realized is that error is comming from where statement after 'and' part.

If i cake out year(n) >= year(CURRENT) -2 then the left join works fine.

What could cause this?, how can i rewrite that statement so it wont thru error.

Re: left join error syntax related

Nicole-Smith — Thu, 12 Jun 2014 21:02:12 GMT

What is CURRENT? Is it a field in the table?

Re: left join error syntax related

Thu, 12 Jun 2014 21:06:31 GMT

Hi,

You ar trying to combine two kinds of script, the first where you tip where function belong to SQL and the second is Qlikview Script (left join (Master)). You can try something like this:

Master_temp:

SQL SELECT

from sometable

where (a='s' or a='p') and year(n) >= year(CURRENT) -2;

no concatenate

Master:

Load * resident Master_temp;

Left join (Master)

Table2:

Load * from mitable2.qvd;

Drop Table Master_temp;

Best regards..

Re: left join error syntax related

its_anandrjs — Thu, 12 Jun 2014 21:10:00 GMT

Try with this way store value after >= in any variable then try

Let vCurrVal = year(CURRENT) - 2;

Master:

SQL SELECT

from sometable

where (a='s' or a='p') and year(n) >= $(vCurrVal);

Left join (Master)

...

Re: left join error syntax related

Joseph_Musekura — Thu, 12 Jun 2014 21:18:37 GMT

Hi,

If you want to deal in your conditions with today’s year – 2, you could play in your condition with this:

Year(YearStart(AddMonths(Today(),-24))) will return 2012
Year(YearStart(AddMonths(Today(),0))) will return 2014
YearStart(AddMonths(Today(),-24)) will return 01-01 -2012
YearStart(AddMonths(Today(),0))) will return 01-01-2014

regards,

Re: left join error syntax related

Thu, 12 Jun 2014 21:23:45 GMT

Anand your solution did not work.

Puzzling part is thing recently change exact code worked before. Not sure how.

Re: left join error syntax related

Thu, 12 Jun 2014 21:24:19 GMT

CURRENT condition is not comming from qlikview it is comming from SQL.

Re: left join error syntax related

Thu, 12 Jun 2014 21:45:21 GMT

It seems that you need named a second table for left join...

Master:

SQL SELECT

from sometable

where (a='s' or a='p') and year(n) >= year(CURRENT) -2;

Table 2:

Left join (Master)

......

I hope to help you

Re: left join error syntax related

Fri, 13 Jun 2014 14:41:13 GMT

Nancy creating new table did not work:

I have changed the sql part to

and year(n) >= year(GetDate())-2;

Re: left join error syntax related

Fri, 13 Jun 2014 14:46:32 GMT

OK, thanks for sharing

Re: left join error syntax related

PrashantSangle — Sat, 14 Jun 2014 09:19:31 GMT

Hi,

Initially go with simple loading sql statement then create New Table using existing table and apply your condition on that and then apply left join on that table.

SQL_Master:

SQL SELECT

Current //ift this is field coming from SQL.

from sometable;

QLIK_MASTER

Load *

Resident SQL_Master

where wildmatch(a,'s','p') and year(n) >= year(AddMonths(CURRENT) -24); //(a='s' or a='p') use wildmatch()

Drop table SQL_Master;

Left Join(Qlik_MASTER)

.......

Hope this will help you.

Regards,