Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

francisco_cohen
New Contributor III

Create a dimension for if not found

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.

1 Solution

Accepted Solutions
vvira1316
Valued Contributor II

Re: Create a dimension for if not found

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.

DraggingQVF into QlikSense.png

BaseMoneyNo.PNG

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);

4 Replies
vvira1316
Valued Contributor II

Re: Create a dimension for if not found

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
New Contributor III

Re: Create a dimension for if not found

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
Valued Contributor II

Re: Create a dimension for if not found

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.

DraggingQVF into QlikSense.png

BaseMoneyNo.PNG

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
New Contributor III

Re: Create a dimension for if not found

Solved!

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

Now it works!

Thank you.

Community Browser