topic Re: How to filter out certain matching data? in QlikView

How to filter out certain matching data?

haymarketpaul — Fri, 30 Nov 2012 10:46:51 GMT

Hi All

QV11 SR1

I have data in the following format...

CustID	Job1	Job2	Job3	Job4
1	SomeJob1	SomeJob2	SomeJob1
2	SomeJob2	SomeJob2		SomeJob4
3	SomeJob1		SomeJob3	SomeJob4
4	SomeJob1			SomeJob1

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

swuehl — Fri, 30 Nov 2012 11:02:22 GMT

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

(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 — Fri, 30 Nov 2012 11:47:03 GMT

Brilliant thank you