Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Uniqe dates - script in Qlik Sense

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
6 Replies
sunny_talwar

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

Not applicable
Author

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

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.

Not applicable
Author

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 ...

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;

Not applicable
Author

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...