Error in load

[#1]

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

[kaushiknsolanki]

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

[Gysbert_Wassenaar]

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.

[stabben23]

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.

[#5]

Hi

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

[Gysbert_Wassenaar]

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.