8 Replies Latest reply: Sep 15, 2015 1:33 AM by Ralf Narfeldt RSS

    iF STATEMENT NOT RECOGNISED

    Clinton Kitt


      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

        • Re: iF STATEMENT NOT RECOGNISED
          Manoj Kulkarni

          Can you post your qlikview application for better understanding ? by the way what is cdocument ?

          • Re: iF STATEMENT NOT RECOGNISED
            Robert Mika

            Are you getting any error message?

            Could you post it?

             

            What  does  the ABC at end of your statement do?

             

            Feeling Qlikngry?

            How To /Missing Manual(19 articles)

            • Re: iF STATEMENT NOT RECOGNISED
              Manoj Kulkarni

              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.

                • Re: iF STATEMENT NOT RECOGNISED
                  Clinton Kitt

                  //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

                    • Re: iF STATEMENT NOT RECOGNISED
                      Ralf Narfeldt

                      Switch the order of the Select and Load statements

                      LOAD....;

                      SQL SELECT...;

                      • Re: iF STATEMENT NOT RECOGNISED
                        kushal chawda

                        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";