Verify the content of one field and replace it under special conditions with another field

[#1]

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

[#2 · Accepted solution]

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.

[#3]

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.

[#4]

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

[#5]

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.

[#6]

If you doesn't want to join you can try with the lookup() function :

https://help.qlik.com/sense/en-us/online/#../Subsystems/Hub/Content/Scripting/InterRecordFunctions/L...