topic Re: Uniqe dates - script in Qlik Sense in App Development

Uniqe dates - script in Qlik Sense

Mon, 25 Jul 2016 13:45:19 GMT

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.

Month	Date	Country	Unique Date
Jun-15	01/06/2015	United States	true
Jun-15	01/06/2015	Germany	false
Jun-15	01/06/2015	France	false
Jun-15	01/06/2015	Hungary	false
Jun-15	02/06/2015	Mexico	true
Jun-15	03/06/2015	United States	true
Jun-15	03/06/2015	United States	false
Jun-15	04/06/2015	Portugal	true
Jun-15	05/06/2015	Colombia	true

Re: Uniqe dates - script in Qlik Sense

sunny_talwar — Mon, 25 Jul 2016 14:00:15 GMT

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

Re: Uniqe dates - script in Qlik Sense

Tue, 26 Jul 2016 06:59:06 GMT

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

Re: Uniqe dates - script in Qlik Sense

sunny_talwar — Tue, 26 Jul 2016 10:57:10 GMT

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

Tue, 26 Jul 2016 12:58:03 GMT

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, 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 — Tue, 26 Jul 2016 15:21:49 GMT

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

Wed, 27 Jul 2016 10:53:48 GMT

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