Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

lightsection
New Contributor III

How to count no. of instance of travel between same countries

Below table shows the source and destination information. I would want to count the no. of instances of travel between 2 cities.

Table.jpg

The desired answer is:

US - Canada = 3

US - Denmark = 2

US - UK = 1

US - Brazil = 1

4 Replies
Employee
Employee

Re: How to count no. of instance of travel between same countries

Put

Source and Destination as Dimension

Count(distinct Destination) as Expression

MVP
MVP

Re: How to count no. of instance of travel between same countries

Maybe like this

LOAD

     Source,

     Destination,

     If(Source precedes Destination, Source & '-' & Destination,Destination &'-'&Source) as Travel

FROM ...

Then create a chart with dimension  Travel and expression

=Count(Travel)

Re: How to count no. of instance of travel between same countries

This is amazing

Re: How to count no. of instance of travel between same countries

I guess the MaxString() or MinString() can also work here, right?

UPDATE: RangeMaxString() or RangeMinString()

UPDATE2: Something like this:

Table:

LOAD *,

  RangeMaxString(Source, Destination) & '-' & RangeMinString(Destination, Source) as Travel;

LOAD * Inline [

Source, Destination

US, Canada

US, Denmark

Canada, US

Denmark, US

UK, US

US, Canada

US, Brazil

];

UPDATE3:

Capture.PNG