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: 
Bart_Breekveldt
Contributor III
Contributor III

Combine where contains

Hi fellow Qlik users,

I want to combine two fields in an expression: ''recipenumber'' and ''description'' which lie in different tables and are connected through a specific number (I have already =[recipenumber]&' '&[description], I want to fit it with an where function). I only want one description per recipenumber (see example below) preferably which contains "HF", otherwise an ohter available description. 

How can I do this? How can I make an where function which returns one desired value? Do I have to script or can I do it with an expression?

Excel Qlik.PNG

Labels (3)
7 Replies
PrashantSangle

try below script
data:
Load * Inline [
id,Description
1, Salomi
2, ABC HF
2, ABC
3, XYZ
3, PQR
3, PQR HF
];

NoConcatenate
test:
Load id as Id,Description Resident data
where WildMatch(Description,'*HF');

Concatenate
LOAD id as Id,Description Resident data
where not Exists(Id,id);

drop table data;

Regards,
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Bart_Breekveldt
Contributor III
Contributor III
Author

It gives an error: "circle reference"

I changed ID to "InkoopRecept" and Description to "Omschrijving", both the Dutch-named columns which I want to combine. Am I doing something wrong?

Thanks

data:
Load * Inline [
InkoopRecept,Omschrijving
1, Salomi
2, ABC HF
2, ABC
3, XYZ
3, PQR
3, PQR HF
];

NoConcatenate
test:
Load InkoopRecept as InkoopRecept,Omschrijving Resident data
where WildMatch(Omschrijving,'*HF');

Concatenate
LOAD InkoopRecept as InkoopRecept,Omschrijving Resident data
where not Exists(InkoopRecept,InkoopRecept);

drop table data;
PrashantSangle

try below
data:
Load * Inline [
InkoopRecept1,Omschrijving
1, Salomi
2, ABC HF
2, ABC
3, XYZ
3, PQR
3, PQR HF
];

NoConcatenate
test:
Load InkoopRecept1 as InkoopRecept,Omschrijving Resident data
where WildMatch(Omschrijving,'*HF');

Concatenate
LOAD InkoopRecept1 as InkoopRecept,Omschrijving Resident data
where not Exists(InkoopRecept,InkoopRecept1);
drop table data;

Regards,
prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Bart_Breekveldt
Contributor III
Contributor III
Author

Circle Reference: One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. Loop(s) will automatically be cut by setting one or more tables as loosely coupled. Use Loosen Table script statement to explicitly declare loosely coupled tables.: return

The error still occurs 😞

I haven't changed anything on the still locked automatic generated section. Below is the only data I've scripted in myself.

Dim:
LOAD * INLINE [
Dim
1
2
];
data:
Load * Inline [
InkoopRecept1,Omschrijving
1, Salomi
2, ABC HF
2, ABC
3, XYZ
3, PQR
3, PQR HF
];

NoConcatenate
test:
Load InkoopRecept1 as InkoopRecept,Omschrijving Resident data
where WildMatch(Omschrijving,'*HF');

Concatenate
LOAD InkoopRecept1 as InkoopRecept,Omschrijving Resident data
where not Exists(InkoopRecept,InkoopRecept1);
drop table data;


PrashantSangle

paste script in 1st tab and then write exit script and try..
If still giving the same error, then post screen shot of it and also post screen shot of script.
Regards,
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Bart_Breekveldt
Contributor III
Contributor III
Author

Qlik Screenshot I.PNGQlik Screenshot II.PNGQlik Screenshot III.PNGQlik Screenshot IV.PNG

Here are the screenshots. I hope this helps to fix the problem

PrashantSangle

remove autogenerated script or move autogenerated script at end.
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂