Discussion board where members can get started with Qlik Sense.
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,
"Topic_Id" as TopicId_weekly;
SQL SELECT "Label_Id",
LOAD Id as TopicId_weekly,
ReferenceId as ReferenceId_weekly,
CategoryId WHERE CategoryId <= '2'';
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;"
"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.
Newbie in Qlik
You can do this kind of transformation in the LOAD statement with the if(condition, if true, if false) syntax.
if(B = '-', C, B) as B;
View solution in original post
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.
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
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.
If you doesn't want to join you can try with the lookup() function :