- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Verify the content of one field and replace it under special conditions with another field
Hello,
I am confronted with another issue and hope I get another hint from you.
I am working with Qilk Sense Desktop and I want to optimize my script for data loading.
I want to add another value "labeltext" (if it is given) in table when the value "ReferenceId_weekly" of the table is not given (Then the value is just "-").
===========Here the important part of my script:==============
LOAD Id as Label_Id,
"Text" as Labeltext;
SQL SELECT Id,
"Text"
FROM Table.dbo."Labels";
LOAD "Label_Id",
"Topic_Id" as TopicId_weekly;
SQL SELECT "Label_Id",
"Topic_Id"
FROM Table.dbo.LabelTopics;
LOAD Id as TopicId_weekly,
ReferenceId as ReferenceId_weekly,
CategoryId WHERE CategoryId <= '2'';
SQL SELECT Id,
CategoryId,
ReferenceId
FROM Table.dbo.Topics;
====================================================
At the moment there are three tables given. Does this matter? Do I need here a join from my tables?
I tried it with:
"IF([ReferenceId_weekly]='-' then ReferenceId_weekly= Labeltext;"
also with:
"IF (ReferenceId_weekly <> '-') then ( ReferenceId_weekly = Labeltext );"
Both had no change in my data.
At this point I am not sure in which level I have to use this (data load script or in the worksheet itself), can you help?
Thank you in advance.
Greetings,
Newbie in Qlik
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You can do this kind of transformation in the LOAD statement with the if(condition, if true, if false) syntax.
e.g. :
LOAD
A,
if(B = '-', C, B) as B;
SQL SELECT
A,
B,
C,
FROM TABLE;
Best regards,
Xavier.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You can do this kind of transformation in the LOAD statement with the if(condition, if true, if false) syntax.
e.g. :
LOAD
A,
if(B = '-', C, B) as B;
SQL SELECT
A,
B,
C,
FROM TABLE;
Best regards,
Xavier.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
ok seems to be the right direction, but now I have another issue:
If I define ReferenceId as sure topic and instead of Labeltext '1'. It works great:
if(ReferenceId= '10660','1',ReferenceId) as ReferenceId_weekly,
So should be like this:
if(ReferenceId= '-',Labeltext,ReferenceId) as ReferenceId_weekly,
Two further questions:
1) How do I have to define Labeltext that it refers to the "Text" from the "Labels"-table? I got the error:
"Field not found - <Labeltext>"
2) How can I check how to define the '-'? It seems to be the wrong value, but my table shows me nothing in the cell and Qilk let me see this in the data model of the table.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Probably the '-' is for NULL values.
So you must ask yourself these questions: Is it normal to have null values for ReferenceId in my topics table ? And how i should manage them ?
You can handle null values with the isnull() function :
If (isnull(ReferenceId), 'Undefined ref', ReferenceId)
Otherwise : Understanding Join, Keep and Concatenate
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so far. Very helpful!
I hoped to avoid using a join method because here I come into a new issue with 3 tables ( I am not aware how to deal with it), but I will try to get more information.
Sometimes the ReferenceId is not given and this is no issue, but I want to see then another field instead.
So I need an Outer Join to have all fields in the same Table and then I can work with the field when I call it in the same LOAD command.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you doesn't want to join you can try with the lookup() function :