Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

1 Solution

Accepted Solutions
Not applicable

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

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.

5 Replies
Not applicable

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

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.

Not applicable

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

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.

Not applicable

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

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

Not applicable

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

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.

Not applicable

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

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...

Community Browser