topic Re: Error in load in QlikView

Error in load

Wed, 25 Nov 2020 16:16:04 GMT

Hi I am try to use a calculate field in a table, but when i try to load it , i have the follow message error

SQL##f - SqlState: 37000, ErrorCode: 156, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'if'.

Data_Temp:

SQL select Lenght_mm, if(rowno()=1,0.05* Lenght_mm+0.95*avg(Lenght_mm), Previous([ValueCurrent])) as Z1

My code in script is more or less this:

ODBC CONNECT TO AirSizing (XUserId is eKSYURFMTTKA, XPassword is MSTKcRFMTTFB);

Data_Temp:

SQL select Lenght_mm, if(rowno()=1,0.05* Lenght_mm+0.95*avg(Lenght_mm), Previous([ValueCurrent])) as Z1;

Data:

load *,if(rowno()>2,0.05* Lenght_mm+0.95*Z1, 0.05* Lenght_mm+0.95*Previous([ValueCurrent])) as ZX

RESIDENT Data_temp;

DROP TABLE Data_temp;

Please what is wrong? Many thanks

Re: Error in load

kaushiknsolanki — Wed, 17 Aug 2016 08:34:06 GMT

Hi,

Try this code.

ODBC CONNECT TO AirSizing (XUserId is eKSYURFMTTKA, XPassword is MSTKcRFMTTFB);

Data_Temp:

Load *, if(rowno()=1,0.05* Lenght_mm+0.95*avg(Lenght_mm), Previous([ValueCurrent])) as Z1;

SQL select Lenght_mm From XYZ;

Data:

load *,if(rowno()>2,0.05* Lenght_mm+0.95*Z1, 0.05* Lenght_mm+0.95*Previous([ValueCurrent])) as ZX

RESIDENT Data_temp;

DROP TABLE Data_temp;

Regards,

Kaushik Solanki

Re: Error in load

Gysbert_Wassenaar — Wed, 17 Aug 2016 08:39:25 GMT

if(rowno()=1

I doubt your SQL Server database supports the rowno() function. If you write SQL then make sure you write only SQL that your database server understands. Don't use Qlikview functions in SQL statements.

Re: Error in load

stabben23 — Wed, 17 Aug 2016 08:39:54 GMT

Hi,

'rowno()' , 'if' and Previous is not valid in a sql Query, those are qlikview related. Normally CASE is used in sql for if.

Maybe you should try to use preceding LOAD, if you not familiar With that just search in the community.

Re: Error in load

Wed, 17 Aug 2016 09:52:13 GMT

Thanks I corrected with Kaushik and your suggestion, so replace the statement rowno()=1, but it not recognize "ValueCurrent". How i can replace it then?

Data_Temp:

load *, if(IDMesurament=1,0.05* Lenght_mm+0.95*avg(Lenght_mm), Previous([ValueCurrent])) as Z1;

SQL SELECT Lenght_mm,IDMesurament FROM AirSizing.dbo."AC30";

Data:

load *,if(IDMesurament>2,0.05* Lenght_mm+0.95*Z1, 0.05* Lenght_mm+0.95*Previous([ValueCurrent])) as ZX

RESIDENT Data_temp;

DROP TABLE Data_temp;

thanks

Re: Error in load

Gysbert_Wassenaar — Wed, 17 Aug 2016 09:59:20 GMT

Data_Temp:

load *, if(IDMesurament=1,0.05* Lenght_mm+0.95*avg(Lenght_mm), Previous([ValueCurrent])) as Z1;

SQL SELECT Lenght_mm,IDMesurament FROM AirSizing.dbo."AC30";

You can only do something with fields that you extract from the source. If you try to do something with a field called ValueCurrent then you first need to extract that in the SQL Select statement. You only retrieve the fields Lenght_mm and IDMesurament. Not a field ValueCurrent.