topic Re: Sort by in QlikView

Sort by

Mon, 31 Oct 2011 16:56:57 GMT

Dears,

I've 4 collumns in my spreadsheet. The first one "contains or not" the text is Job1, the second one contains or not the text Job2, in the third one, Job3 and in the last one Job4.

This data cames from our internet system, that asked to our customers what is his funcion at the company.

So, in the Collumn 1, we will have on only the values "Job1" or nothing "blank cells".

But the same customer can have more than 1 Job, so, the following situation can occour:

Customer	Job1	Job2	Job3	Job4
Customer1	Job1	Job2	Job3	Job4
Customer2		Job2	Job3	Job4
Customer3			Job3	Job4
Customer4				Job4
Customer5	Job1
Customer6		Job2
Customer7			Job3
Customer8				Job4
Customer9	Job1		Job3
Customer10		Job2		Job4

So, what I woud like to do is to create a list box with the information (Job1, Job2, Job3 and Job4 only). And when I select one of this options the system will filter to me the customers that had choosed this Job.

Example: Imagine that I've sorted Job3, so the customers that will be shown is:

Customer	Job1	Job2	Job3	Job4
Customer1	Job1	Job2	Job3	Job4
Customer2		Job2	Job3	Job4
Customer3			Job3	Job4
Customer7			Job3
Customer9	Job1		Job3

So, how can I do it?

It's not a concatenation...

Did you get it?

Thanks for your attention

Bye

Re: Sort by

swuehl — Mon, 31 Oct 2011 17:55:36 GMT

Hi,

maybe like attached?

I used a crosstable load to get your table in and then created a pivot table with expression:

=only({<Customer = p({<Data=p(Jobs) >} Customer ),Jobs= >} Data)

Hope this helps,

Stefan

Sort by

Mon, 31 Oct 2011 18:37:11 GMT

Sorry, but I didn't understand!

Can you do another example for me?

Sort by

swuehl — Mon, 31 Oct 2011 18:56:04 GMT

Could you take a look at above attached sample file or do you work with a personal edition?

Sort by

Mon, 31 Oct 2011 19:32:59 GMT

Thank you so much for your assistance but I didn't understand it yet!

Canyou do an example using excel!

Sort by

swuehl — Mon, 31 Oct 2011 20:07:20 GMT

Yes I could do also an example using excel, but the structure of my sample excel file would be the same, wouldn't it (unless you specify another data structure)?

This is my script:

Crosstable:

CrossTable(Jobs, Data)

LOAD Customer,

Job1,

Job2,

Job3,

Job4

FROM

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

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

Result:

NOCONCATENATE LOAD Customer, Jobs, if(Data='',NULL(), Data) as Data resident Crosstable;

drop table Crosstable;

If I would use an excel file, the only difference would probably be the two lines directly after FROM, where I specify to load your above table from the web.

The following resident table load is only used to remove some unwanted blank values from the data, you may or may not need to use this for your real data (probably not, if you load from DB).

As you see, I use a crosstable load, please take a look in the manual on how this works, it transforms a crosstable back to "standard" data layout.

How to create a pivot table chart with the two dimensions Customer and Jobs is clear, isn't it?

Then I used this as only expression:

=only({<Customer = p({<Data=p(Jobs) >} Customer ),Jobs= >} Data)

This is a set analyis expression, this looks a bit complicated at first, but it says just

Select all customer who have a relation with the selected Jobs, then clear the selection on jobs to allow viewing all jobs (both selections are only in the context of the table object's expression, this is not a selection that will affect other objects).

Hope this clears some points, if not, please clarify your problems.

Regards,

Stefan

Re: Sort by

Thu, 03 Nov 2011 13:19:15 GMT

Sorry, but it's my first time using QlikView and it's so advanced for me!

Follow attached is an example in Excel.

Can you do the same thing for me with this spreadsheet?

I need a list with only 4 Options (Manager, Administrator, Engineer and Marketing). But as you can see they are listed in 4 different collumns. And what I need is this list sorting the values!

Thanks for your attention,

Best regars,

Re: Sort by

swuehl — Thu, 03 Nov 2011 13:44:44 GMT

Please check attached.

Hope this helps,

Stefan