10 Replies Latest reply: Aug 8, 2012 5:23 AM by justynak RSS

    load where like list of conditions

      Hi all,

      can you help with my trouble?

       

      I have a list of orders with descriptions in one table.

       

      The other table which is not linked to the first one presents a list of 90 words that I should look for in the order description.

       

      I need to

      load First table

      where order_desc like '*90 words from the second file*'

       

      Can you give me any idea how i can avoid using like '*xxxx*' 90 times and have it in one go?

       

      Thanks a lot for any hint.

      Justyna

        • Re: load where like list of conditions

          have you tried keep function in script?

          • Re: load where like list of conditions
            sunil jain

            you can write following code in where condition.

             

            where Right(order_desc,2) ='90' from the second file;

            • Re: load where like list of conditions
              Miguel Angel Baeyens de Arce

              Hi,

               

              The WildMatch() function will do in the WHERE statement with some tricky loading:

               

              // The following creates a '*Value*', '*Value2*' string in one field
              Table2Temp:
              LOAD Chr(39) & Chr(42) & Concat(DISTINCT ID, Chr(42) & Chr(39) & Chr(44) & Chr(39) & Chr(42)) & Chr(42) & Chr(39) AS AllValuesToLoad
              // whatever source here
              
              LET vAllValuesToBeLoaded = FieldValue('AllValuesToLoad', 1);
              
              Table1:
              LOAD *
              FROM File.qvd (qvd)
              WHERE WildMatch(ID2, '$(vAllValuesToBeLoaded)');
              

               

              Hope that helps.

               

              Miguel

                • Re: load where like list of conditions

                  Hi Miguel,

                  I think we are close, but i have errors in the script when using your formula. DO you know the reason of that?

                   

                   

                   

                  1.bmp

                    • Re: load where like list of conditions
                      Miguel Angel Baeyens de Arce

                      Hi,

                       

                      They are not actual errors rather than unexpected behavior of the expression editor checker. Concat() function allows as a second parameter not only single quote delimited separators, but expressions as well. That should work just fine when you reload it, regardless the expression editor.

                       

                      Hope that helps.

                       

                      Miguel

                        • Re: load where like list of conditions

                          Hi Miguel,

                          thank you for clarification and the test file.

                          I did exactly the same and can see that the first table is created with one line and all descriptions.

                           

                          '*ACADEMY*','*ASSESSMENT*','*ASSESSMENTS*','*CAREER*','*CAREER PATH*','*CLASS*','*CLASSES*','*CLASSROOM*','*CLASSROOMS*','*COACH*','*COACHING*','*COURSE*','*COURSES*','*CURRICULA*','*CURRICULUM*','*CURRICULUMS*','*DEVELOPED*','*DEVELOPING*','*DEVELOPMENT*','*DEVELOPS*','*E-LEARNING*','*E-ROOM*','*E-ROOMS*','*ENROLMENT*','*ENROLMENTS*','*EROOM*','*EXPERT*','*EXPERTISE*','*HONORARIUM*','*INFORMATION*','*KNOWLEDGE*','*KNOWLEDGE MANAGEMENT*','*L&D*','*LEADERSHIP*','*LEARN*','*LEARNED*','*LEARNING*','*LEARNING MANAGEMENT SYSTEM*','*LEARNINGS*','*LEARNS*','*LECTURE*','*LECTURER*','*LECTUROR*','*LEVEL*','*LEVELS*','*LMS*','*MENTOR*','*MODULE*','*MODULES*','*MONITORING*','*MONITORS*','*MOODLE*','*MOODLES*','*PERFORMANCE*','*PERFORMING*','*PORTAL*','*ROLL-OUT*','*SEMINAR*','*SEMINARS*','*SKILL*','*SKILLS*','*STANDALONE*','*STUDIES*','*STUDY*','*SUCCESSION*','*TALENT*','*TALENTING*','*TNO*','*TNOP*','*TRAINEE*','*TRAINEES*','*TRAINER*','*TRAINING*','*TRAININGS*','*TRAINS*','*TUTOR*','*UNIVERSIDAD*','*UNIVERSITA*','*UNIVERSITAT*','*UNIVERSITE*','*UNIVERSITEIT*','*UNIVERSITET*','*UNIVERSITY*','*UNIVERSITÄT*','*WORKSHOP*','*WORKSHOPS*'

                           

                          However, I am out of idea why I get the error in the second table (as below). Can you help? thanks

                           

                          Error in expression:

                          ')' expected

                          Table1:

                          LOAD *

                          FROM D:\A2A_Qlikview\Production\3_Backend\QVDs\EKPO.qvd (qvd)

                          WHERE FiscY = 2012 and WildMatch(Prod_dsc, '''*ACADEMY*'',''*ASSESSMENT*'',''*ASSESSMENTS*'',''*CAREER*'',''*CAREER PATH*'',''*CLASS*'',''*CLASSES*'',''*CLASSROOM*'',''*CLASSROOMS*'',''*COACH*'',''*COACHING*'',''*COURSE*'',''*COURSES*'',''*CURRICULA*'',''*CURRICULUM*'',''*CURRICULUMS*'',''*DEVELOPED*'',''*DEVELOPING*'',''*DEVELOPMENT*'',''*DEVELOPS*'',''*E-LEARNING*'',''*E-ROOM*'',''*E-ROOMS*'',''*ENROLMENT*'',''*ENROLMENTS*'',''*EROOM*'',''*EXPERT*'',''*EXPERTISE*'',''*HONORARIUM*'',''*INFORMATION*'',''*KNOWLEDGE*'',''*KNOWLEDGE MANAGEMENT*'',''*L&D*'',''*LEADERSHIP*'',''*LEARN*'',''*LEARNED*'',''*LEARNING*'',''*LEARNING MANAGEMENT SYSTEM*'',''*LEARNINGS*'',''*LEARNS*'',''*LECTURE*'',''*LECTURER*'',''*LECTUROR*'',''*LEVEL*'',''*LEVELS*'',''*LMS*'',''*MENTOR*'',''*MODULE*'',''*MODULES*'',''*MONITORING*'',''*MONITORS*'',''*MOODLE*'',''*MOODLES*'',''*PERFORMANCE*'',''*PERFORMING*'',''*PORTAL*'',''*ROLL-OUT*'',''*SEMINAR*'',''*SEMINARS*'',''*SKILL*'',''*SKILLS*'',''*STANDALONE*'',''*STUDIES*'',''*STUDY*'',''*SUCCESSION*'',''*TALENT*'',''*TALENTING*'',''*TNO*'',''*TNOP*'',''*TRAINEE*'',''*TRAINEES*'',''*TRAINER*'',''*TRAINING*'',''*TRAININGS*'',''*TRAINS*'',''*TUTOR*'',''*UNIVERSIDAD*'',''*UNIVERSITA*'',''*UNIVERSITAT*'',''*UNIVERSITE*'',''*UNIVERSITEIT*'',''*UNIVERSITET*'',''*UNIVERSITY*'',''*UNIVERSITÄT*'',''*WORKSHOP*'',''*WORKSHOPS*''')