Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Users, make your voice heard! Participate in Wisdom of Crowds® Market Study: Start Survey
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
MVP & Luminary
MVP & Luminary

Hi @klrameet ,

As @Kush 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
Kush
MVP
MVP

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

klrameet
Contributor III
Contributor III

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

klrameet
Contributor III
Contributor III

@Kush  ... 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
MVP & Luminary
MVP & Luminary

Hi @klrameet ,

As @Kush 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

klrameet
Contributor III
Contributor III

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