Case statement in Load Editor with In SQL Statement

[klrameet]

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'

[Kushal_Chawda]

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

[klrameet]

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

[klrameet]

@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 · Accepted solution]

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]

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