Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created a qlikview doc on my local version with an ODBC connection to SQL Server view
SELECT
ArchiveFlag,
Code,
FROM sqlvw."ABC_long";
LOAD
ArchiveFlag,
Code,
if(Code>500 or Code=200, '>500','<500') AS Report,
ABC;
The issue I have is the if statement is not being recognised on the server version when I transfer the cdocument form my local PC?
So then the field 'Report" is not available as an option ?
Any suggestions?
Thanks
Switch the order of the Select and Load statements
LOAD....;
SQL SELECT...;
Can you post your qlikview application for better understanding ? by the way what is cdocument ?
Are you getting any error message?
Could you post it?
What does the ABC at end of your statement do?
Feeling Qlikngry?
Difficult to post application as I said it links to SQL vw so would not show up, sorry about the typo - I meant when I transfer the qvw from my local PC to the server environment
No, there is no error message it just doesn't appear to read the if statement and create the field "Report" (with teh values <500 or >500, which is critical. The ABC is just another field I am bringing in
Are you trying to do preceding load to select statement , if so then you should have got error for column ABC ?
How are you getting column ABC ? Please can you provide full script, so we can have better understanding to provide solution.
//OLEDB CONNECT
SQL SELECT
ArchiveFlag,
Code,
CodeDescription,
CurrentSuspTotalDays,
FinancialPeriod,
FinancialYearPeriod,
MainIdentifier,
PreviousSuspTotalDays,
ReferenceDescription,
ReferenceIdentifier,
ReportedRemovalDateTime,
ReportedRemovalReasonCode,
TotalWaitingDays,
MonthNameShort,
WaitingListType
FROM xxx.dbo."ListDetailed";
LOAD ArchiveFlag,
Code,
if(Code>500 or Code=200, '>500','<500') AS Report,
CodeDescription,
CurrentSuspTotalDays,
FinancialPeriod,
FinancialYearPeriod,
MainIdentifier,
PreviousSuspTotalDays,
ReferenceDescription,
ReferenceIdentifier,
ReportedRemovalDateTime,
ReportedRemovalReasonCode,
if (ISNUll(ReportedRemovalReasonCode),'NULL',(ReportedRemovalReasonCode))AS Removal,
TotalWaitingDays,
MonthNameShort,
WaitingListType;
I have removed identifiable infomration fromthe script but this is essentially the key components. It is the two bold fields that are not created when I move to the qvw to the Qlikview Server. As I said they are loaded when on my local environment
Switch the order of the Select and Load statements
LOAD....;
SQL SELECT...;
Sequence of your load statement is wrong
First you load the data from data base using SQL SELECT.. then use preceding load on top of that to do some transformation.
So, it should be
LOAD ArchiveFlag,
Code,
if(Code>500 or Code=200, '>500','<500') AS Report,
CodeDescription,
CurrentSuspTotalDays,
FinancialPeriod,
FinancialYearPeriod,
MainIdentifier,
PreviousSuspTotalDays,
ReferenceDescription,
ReferenceIdentifier,
ReportedRemovalDateTime,
ReportedRemovalReasonCode,
if (ISNUll(ReportedRemovalReasonCode),'NULL',(ReportedRemovalReasonCode))AS Removal,
TotalWaitingDays,
MonthNameShort,
WaitingListType;
SQL SELECT
ArchiveFlag,
Code,
CodeDescription,
CurrentSuspTotalDays,
FinancialPeriod,
FinancialYearPeriod,
MainIdentifier,
PreviousSuspTotalDays,
ReferenceDescription,
ReferenceIdentifier,
ReportedRemovalDateTime,
ReportedRemovalReasonCode,
TotalWaitingDays,
MonthNameShort,
WaitingListType
FROM xxx.dbo."ListDetailed";