Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
klrameet
Contributor III
Contributor III

Case statement in Load Editor with In SQL Statement

Getting Below Error:

The following error occurred:
Unexpected token: 'WHEN', expected one of: ')', 'Where', 'While', ':', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', ...
 
For the Data Load Script:LOAD
[Date],
[EmpID],
[Alternate Employee ID],
[PID],
[Last Name],
[First Name],
IF([EmpID] = 'E2547','N2L3', CASE WHEN [EmpID] = 'E2547' THEN 'N2L3'
WHEN [EmpID] IN (SELECT DISTINCT [EmpID] FROM [lib://AttachedFiles/Report.xlsx]
WHERE [Supervisor ID] = 'E2547' AND [Employment Status] = 'A') THEN 'N3L4'
WHEN [EmpID] IN (SELECT DISTINCT [EmpID]
FROM [lib://AttachedFiles/Report.xlsx]
WHERE [Supervisor ID] IN (SELECT DISTINCT [EmpID]
FROM [lib://AttachedFiles/Report.xlsx]
WHERE [Supervisor ID] = 'E2547' AND [Employment Status] = 'A')
AND [Employment Status] = 'A'
) THEN 'N4L5'
END CASE AS [N_L]
FROM
FROM [lib://AttachedFiles/Report.xlsx]
WHERE
[Employment Status] = 'A'
CONNECT BY
PRIOR [EmpID] = [Supervisor ID]
START WITH [EmpID] = 'E2547'
Labels (5)
1 Solution

Accepted Solutions
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @klrameet ,

As @Kushal_Chawda has rightly identified you are loading from a spreadsheet, rather than a SQL connection. You therefore need to use Qlik syntax, rather than SQL syntax. I think you therefore need to make more changes than just the case to make this work with that data source.

To tackle the CASE statement first, instead of using CASE WHERE Qlik syntax uses a nested IF statement. You seem to be be blending both.

Qlik doesn't allow nested sub-queries like SQL, so having separate loads in a single table doesn't work. You need to use LEFT JOIN or associations to have data from multiple tables.

You have a duplicated FROM statement.

CONNECT BY is not part of Qlik syntax.

There is no format specifier for the spreadsheet, this should appear in brackets after the spreadsheet name.

My guess is that you have some SQL which works in the source database, with table names, and have swapped out the table name to refer to the spreadsheet instead. This won't work. You either need to connect to the database in Qlik and issue the SQL statement as it was originally, or build a totally different load statement based on the spreadsheet.

Hope that helps.

Steve

View solution in original post

5 Replies
Kushal_Chawda

It doesn't seems to be SQL query..   Is it error? FROM [lib://AttachedFiles/Report.xlsx]

klrameet
Contributor III
Contributor III
Author

it’s the file & path from where the data will be loaded 

klrameet
Contributor III
Contributor III
Author

@Kushal_Chawda  ... I tried small section of the script to see if it would work ... but unable to make any progress as finding it difficult to compare field from file using SQL as below .. its not working. any pointers would be helpful

 

Load
[EmpID],
[Supervisor ID],
IF([EmpID] = '5668255','N2L3',IF([EmpID]=SELECT [EmpID] FROM [lib://AttachedFiles/Report.xlsx] WHERE [Supervisor ID] = '25R7' AND [Employment Status] = 'A', 'N3L4',''))

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @klrameet ,

As @Kushal_Chawda has rightly identified you are loading from a spreadsheet, rather than a SQL connection. You therefore need to use Qlik syntax, rather than SQL syntax. I think you therefore need to make more changes than just the case to make this work with that data source.

To tackle the CASE statement first, instead of using CASE WHERE Qlik syntax uses a nested IF statement. You seem to be be blending both.

Qlik doesn't allow nested sub-queries like SQL, so having separate loads in a single table doesn't work. You need to use LEFT JOIN or associations to have data from multiple tables.

You have a duplicated FROM statement.

CONNECT BY is not part of Qlik syntax.

There is no format specifier for the spreadsheet, this should appear in brackets after the spreadsheet name.

My guess is that you have some SQL which works in the source database, with table names, and have swapped out the table name to refer to the spreadsheet instead. This won't work. You either need to connect to the database in Qlik and issue the SQL statement as it was originally, or build a totally different load statement based on the spreadsheet.

Hope that helps.

Steve

klrameet
Contributor III
Contributor III
Author

tahnks everyone, i figured that its not worth the effort , doing it in qlik and rather focus on getting the data some where at the source