7 Replies Latest reply: Oct 11, 2011 7:18 AM by Claudia Zwick RSS

    problem with if function

    justynak Aug 26, 2011 5:13 AM

      Hi, I'am new to QLikView and do not have much in common with coding.. Could anyone help me with the case:

      I have data loaded from two Excel sheets (let's say 'Excel1' and 'Excel2'). Then I would like to create classification based on comparison of dates (each one from different Excel). The names are: ‘Rev. rec. date’ and ‘Date (Installation)’

       

      Under loaded fields in Excel2 I wrote:

      // classification

      If ([Rev. rec. date]) <= [Date (Installation)], ‘correct’, ‘incorrect’) as Classification

       

      However, during load I receive comment that field [Rev. rec. date] was not found and I assume that it may be because it comes from different load of data. How should I indicate in this function that I refer to Excel1?

       

      Thanks in advance for any tip.

       

      • 930 Views
      • Tags:
        • problem with if function
          Claudia Zwick Aug 26, 2011 5:32 AM (in response to justynak)

          Hi justynak,

           

          Yes, you have to concate the two tables.

           

          For example:

           

          Table1:

          LOAD

               Date1,

               Date2,

               Rev. rec. date

          FROM Excel1;

           

          JOIN LOAD

               Date1,

               Date3,

               Date (Insatallation)

          FROM Excel2;

           

          Then you have a table with the fields Date1, Date2, Date3, Rev. rec. date, Date (Installation) and then you could do you if-statement. The joining of two tables based on one field, which is equal in each excel-sheets.

           

          Table2:

          LOAD

               Date1,

               Date2,

               Date3,

               if([Rev. rec. date] <= [Date (Installation)], 'correct', 'incorrect') AS Classification

               RESIDENT Table1;

           

          DROP TABLE Table1; //otherwise you have to rename the fields in Table2 with AS

           

          Regards

          vicky

            • problem with if function
              mhamberg Oct 10, 2011 4:52 PM (in response to Claudia Zwick)

              Hi vicky, I'm reading this thread and I have a question about your use of Resident in conjunction with the joins above as I've seen this elsewhere and I'm wondering if this might solve a problem I'm having.

               

              In the case above, why couldn't justynak put the IF statement right in the table 1 load like:

               

              Table1:

              LOAD

                   Date1,

                   Date2,

                   if([Rev. rec. date] <= [Date (Installation)], 'correct', 'incorrect') AS Classification

              FROM Excel1;

               

              Does that cause some kind of problem in Qlikview? I am basically doing that in my script but I'm seeing weird things happen like tables that won't join properly. I'm wondering if it's some limitation in the software.

               

              Thanks

                • problem with if function
                  Claudia Zwick Oct 11, 2011 4:15 AM (in response to mhamberg)

                  Hello mhamberg,

                   

                  The problem is, that the field Date (Installation) is not a field in the first Excel-document, so you get the Error 'Field not found'.

                  That's why I have propose to connect this to tables in one table. Then the if-statement will work.

                   

                  Joining tables works fine, if the tables have one or more field names eqal. Therefor John has giving a quite good post: http://community.qlik.com/thread/30318

                   

                  regards vicky

              • problem with if function
                Vijit Jindal Oct 11, 2011 5:07 AM (in response to justynak)

                hi justynak,

                you can also do like this:

                 

                1.load data from both the excel sheets.

                2.make qvd's of both the tables:

                 

                eg:  store tablename into qvdname.qvd;

                 

                3.fetch data from both qvd's under one table by join.

                4. then you can compare the two fields from different tables.

                 

                hope this will help you.

                • problem with if function
                  Vincent Ardiet
                  Vincent Ardiet Oct 11, 2011 5:15 AM (in response to justynak)

                  Hi Vicky,

                   

                   

                  Have you try to use the Peek or Lookup functions ?

                  We don't know the associative link between your 2 tables but with those functions you will be able to read a value from another table without joining it.

                   

                  Regards,

                  Vincent