Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Justyna,
Remove the single quotes as follows.
WHERE WildMatch(ID2, $(vAllValuesToBeLoaded)); // no single quotes wraping the variable
Hope that does the trick.
Miguel
have you tried keep function in script?
you can write following code in where condition.
where Right(order_desc,2) ='90' from the second file;
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
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?
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
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*''')
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
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.
Hi Justyna,
Remove the single quotes as follows.
WHERE WildMatch(ID2, $(vAllValuesToBeLoaded)); // no single quotes wraping the variable
Hope that does the trick.
Miguel