6 Replies Latest reply: Jul 27, 2016 6:53 AM by Barbara Michalowska RSS

    Uniqe dates - script in Qlik Sense

    Barbara Michalowska

      Hi all,

       

      I am struggling with Qlik Sense last few weeks as I am new user. I hope some of you can help.

       

      I have excel file uploaded to Qlik which includes formulas...however recently I realized Qlik doesn't read Excel formulas at all...just a static data.

      Basically my file contains data with high priority problems (Critical and High) opened for multiple regions globally on last 13 months. There are cases that on the same day we had few high problems opened.

       

      I would like to add a new column, using script when loading data, that would show me the unique dates, when high priority problem occurs (also when  filtering different region), know as EVENT FREE DAYS.

       

      Please see example below.

       

      MonthDateCountryUnique Date
      Jun-1501/06/2015United Statestrue
      Jun-1501/06/2015Germanyfalse
      Jun-1501/06/2015Francefalse
      Jun-1501/06/2015Hungaryfalse
      Jun-1502/06/2015Mexicotrue
      Jun-1503/06/2015United Statestrue
      Jun-1503/06/2015United Statesfalse
      Jun-1504/06/2015Portugaltrue
      Jun-1505/06/2015Colombiatrue
        • Re: Uniqe dates - script in Qlik Sense
          Sunny Talwar

          What would be the expected output look like for the sample data provided above?

            • Re: Uniqe dates - script in Qlik Sense
              Barbara Michalowska

              The expected output is last column : "Unique Date".

                • Re: Uniqe dates - script in Qlik Sense
                  Sunny Talwar

                  Try this:

                   

                  Table:

                  LOAD *,

                  If(Date = Previous(Date), 'False', 'True') as [Unique Date]

                  INLINE [

                      Month, Date, Country

                      Jun-15, 01/06/2015, United States

                      Jun-15, 01/06/2015, Germany

                      Jun-15, 01/06/2015, France

                      Jun-15, 01/06/2015, Hungary

                      Jun-15, 02/06/2015, Mexico

                      Jun-15, 03/06/2015, United States

                      Jun-15, 03/06/2015, United States

                      Jun-15, 04/06/2015, Portugal

                      Jun-15, 05/06/2015, Colombia

                  ];

                   

                  I am not sure if you data is sorted right or not, but Previous/Peek functions are highly dependent on sorting of your data.

                    • Re: Uniqe dates - script in Qlik Sense
                      Barbara Michalowska

                      I can easily sort it right.

                       

                      Question for second part of formula...

                      INLINE [

                          Month, Date, Country

                          Jun-15, 01/06/2015, United States

                          Jun-15, 01/06/2015, Germany

                          Jun-15, 01/06/2015, France

                          Jun-15, 01/06/2015, Hungary

                          Jun-15, 02/06/2015, Mexico

                          Jun-15, 03/06/2015, United States

                          Jun-15, 03/06/2015, United States

                          Jun-15, 04/06/2015, Portugal

                          Jun-15, 05/06/2015, Colombia

                      ];

                       

                      do I have to copy here all this data ? I have around 3000 cells there ...

                        • Re: Uniqe dates - script in Qlik Sense
                          Sunny Talwar

                          You don't. This was just to demonstrate the idea... in your case, you will only want to do this

                           

                          Table:

                          LOAD Month,

                              Date,

                              Country,

                              If(Date = Previous(Date), 'False', 'True') as [Unique Date]

                          FROM Source....

                           

                          Again make sure to check the sorting which cannot be done from a file load. To do the proper sorting you will need to take a resident load

                           

                          Table:

                          LOAD Month,

                              Date,

                              Country

                          FROM Source....

                           

                          FinalTable:

                          LOAD *,

                              If(Date = Previous(Date), 'False', 'True') as [Unique Date]

                          Resident Table

                          Order By .....;

                           

                          DROP Table Table;

                            • Re: Uniqe dates - script in Qlik Sense
                              Barbara Michalowska

                              I didn't sort in my file as data are in the right order from the beginning.

                              My 'load' is below, please let me know if something is wrong...

                               

                              LOAD

                                  Number,

                                  Priority,

                                  "Assignment group",

                                  Location,

                                  Opened,

                                  Duration,

                                  Closed,

                                  "Division primary",

                                  Region,

                                  "CI Category",

                                  "CI Classification",

                                  "CI Type",

                                  Category,

                                  Subcategory,

                                  "CI Class",

                                  "Configuration item",

                                  "Month",

                                  Site,

                                  "Date",

                                  Country,

                                  "EMEA Region",

                                  "GSL ID",

                                  #Days,

                                  If(Date = Previous(Date), 'False', 'True') as [Unique Date]

                                

                              FROM [lib://test/QS-Problems.xls]

                              (biff, embedded labels, table is Problems$);

                               

                               

                              I think it doesn't work properly....

                              Here it is how it looks like without using any filter in Qlik....seems not bad however....

                              Here I used filter only for one region and data are not matching...