Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hi Justyna,

Remove the single quotes as follows.

WHERE WildMatch(ID2, $(vAllValuesToBeLoaded)); // no single quotes wraping the variable

Hope that does the trick.

Miguel

View solution in original post

10 Replies
Not applicable
Author

have you tried keep function in script?

suniljain
Master
Master

you can write following code in where condition.

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

Miguel_Angel_Baeyens

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

Not applicable
Author

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

Miguel_Angel_Baeyens

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

Not applicable
Author

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*''')

Miguel_Angel_Baeyens

Hi,

It seems it's adding more single quotes than needed, therefore the error. Make sure the string in the variable returns as you hav ein your first line:

'*Value*','*Value2*'

Hope that helps.

Miguel

Not applicable
Author

Hi Miguel,

I tested the file that you have attached - but still I think there is some error.

I am attaching to the original post v.2 of your test file with example xlsx file which i am loading. Do you recognize the source of the error now?

many thanks

j.

Miguel_Angel_Baeyens

Hi Justyna,

Remove the single quotes as follows.

WHERE WildMatch(ID2, $(vAllValuesToBeLoaded)); // no single quotes wraping the variable

Hope that does the trick.

Miguel