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.

haymarketpaul
Contributor III

How to filter out certain matching data?

Hi All

QV11 SR1

I have data in the following format...

CustIDJob1Job2Job3Job4
1SomeJob1SomeJob2SomeJob1
2SomeJob2SomeJob2SomeJob4
3SomeJob1SomeJob3SomeJob4
4SomeJob1SomeJob1

So each Customer can have from 0-4 jobs.

I need to be able to show in a straight table or whatever, customers that have more than 1 different type of job.

So in the example data above i would need to show CustID 1,2,3 but not 4 (only one type of job).

I would also like to suppress jobs that are the same, for example

For CustID 1 i would just show SomeJob1 (once) and SomeJob2

For CustID 2 i would just show SomeJob2 (once) and SomeJob4

For CustID 3 i would just show SomeJob1, SomeJob3 and SomeJob4

CustID 4 would not be shown at all

Any ideas welcome this is driving me slightly mad

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: How to filter out certain matching data?

First, I would try to get your table from a Crosstable structure into a straight table using CROSSTABLE in the script:

CROSSTABLE (JobNr, JobType) LOAD CustID,

     if(trim(len(Job1)),Job1) as Job1,

     if(trim(len(Job2)),Job2) as Job2,

     if(trim(len(Job3)),Job3) as Job3,

     if(trim(len(Job4)),Job4) as Job4

FROM

[http://community.qlik.com/thread/67416?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

In the front end, just create a straight table with dimension CustID and as expression:

=if(isnull(only(JobType)), concat(DISTINCT JobType,', ' ) )


Hope this helps,

Stefan

2 Replies
MVP
MVP

Re: How to filter out certain matching data?

First, I would try to get your table from a Crosstable structure into a straight table using CROSSTABLE in the script:

CROSSTABLE (JobNr, JobType) LOAD CustID,

     if(trim(len(Job1)),Job1) as Job1,

     if(trim(len(Job2)),Job2) as Job2,

     if(trim(len(Job3)),Job3) as Job3,

     if(trim(len(Job4)),Job4) as Job4

FROM

[http://community.qlik.com/thread/67416?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

In the front end, just create a straight table with dimension CustID and as expression:

=if(isnull(only(JobType)), concat(DISTINCT JobType,', ' ) )


Hope this helps,

Stefan

haymarketpaul
Contributor III

Re: How to filter out certain matching data?

Brilliant thank you