Create a dimension for if not found

[francisco_cohen]

Hi all,

I'm using qlik sense desktop.

Through the script, I'm loading 2 sources of data:

[Sales Funnel]:

LOAD

    "Project name",

    money("Price EUR")

FROM [lib://Fsbox Sales Funnel/Output\Output_AA.xlsx]

(ooxml, embedded labels, table is Leads);

[Sales Funnel Baseline]:

LOAD

    "Project name",

    "Price EUR" as "SF Baseline Price EUR"

FROM [lib://Fsbox Sales Funnel/Sales Funnel Baseline.xlsx]

(ooxml, embedded labels, table is Sheet1);

The list of projects in both table are not equal so I want to create a dimension in my dashboard (not in the script), that will check if the project exists in the other table. I'm doing like this but it doesn't works:

=if(isnull([SF Baseline Price EUR]),'Yes')

I beleive it doesn't works because the value is not null neither 0... it just doesn't exists...

How can I solve this?

Thks,

FC.

[vvira1316]

Hi,

I'm not sure if you are meaning to define measure or dimension. I've created a sample Actual (Output) and Baseline data file to be used in the app. Attached please find them. Advise if it is same as your scenario. If not explain what it should be and what is expected output.

Regards,

Vijay

[francisco_cohen]

Hi Vijay,

Thank you for your reply.

I'm using Qlik sense desktop, so I couldn't use your qvf file to check what you've done.

[vvira1316 · Accepted solution]

Hi,

You will have to save the document on your drive. Sign in into your QlikSense Desktop app.

Then open the saved file by dragging it into QlikSense as it can be seen in screenshot below.

Script is as follows. Which is similar to yours

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

LOAD
    Project,
    "Money"
FROM [lib://DataFile2/Actual.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD
    Project,
    "Base Money"
FROM [lib://DataFile2/BaseLine.xlsx]
(ooxml, embedded labels, table is Sheet1);

[francisco_cohen]

Solved!

The issue is that I was crwating a dimension instead of a measure.

Now it works!

Thank you.