4 Replies Latest reply: Sep 18, 2017 12:47 PM by Francisco Cohen RSS

    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.

        • Re: Create a dimension for if not found
          Vijay Vira

          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

            • Re: Create a dimension for if not found
              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.

                • Re: Create a dimension for if not found
                  Vijay Vira

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