Beginners Tutorial

[ruanhaese]

Hi Everyone

Im very new to Qlikview and need some help. Having gone through some projects

I seem to be struggling a bit with the basics on principles of the set analysis.

I need some help with designing the set analysis formulae.

What Ive done is Ive made a very basic "test", its got a few questions  that go through

some common problems I keep encountering.

For example: Get the name of the largest fish from the dataset or Get all the Fish that start with the letter B

The data is 10 rows so its quite easy to workout the answers manually -

please note the data was just thumb-sucked, its really not accurate.

There are a total of 10 questions, Ive only been able to get 3 right myself (marked in green).

Most of it is trying to get a list of rows with filters XYZ.

Here are the questions (with Results so you can see what I'm aiming for)

1)     Show Fish by Oceans [Bar chart]

Result:  Pacific 1200, Atlantic 1650, Indian 3200

2)     Show amount of fish > 600 mm [Table]

Result:   6 rows (Barracuda, Squid, Blue, Trumpet, Drummer, Yellow)

3) Name of the Ocean with the most amount of fish [Text object]

Result:  Indian

4)     Show all the Fish in the Indian ocean with Amount >= 1000 [Table]

Result:  2 rows (Trumpet, Yellow)

5) Show all the Fish discovered after 01 Jan 1980 [Table]

Result: 3 rows (Barracuda, Tuna, Red)

6) Fish starting with the letter B [Table]

Result: 3 rows (Barracuda, Box, Blue)

7) Name of the Size of the biggest fish in Pacific ocean [Text Object]

Result:  Squid (5000mm)

😎 Count amount of names in the Atlantic ocean starting with B [Table]

Result:  2 rows (Box, Blue)

9) Show all the rows (ie no filters) in a table

Result:  All 10 rows displayed

10) Rank the Ocean by how many fish there are in it (Table)

Result:  (1) Indian – 4, (2) Atlantic - 3, (3) Pacific - 3

* If any of the questions seem unclear please let me know.

There should be 2 files attached - the Qlikview file and the Excel doc (source)

  Any assistance with this would really be appreciated.

[Saravanan_Desingh · Accepted solution]

Please find attached. I tried my best. !

[Anonymous]

That looks like a good tutorial - never seen it before, but not sure it is a "beginners" tutorial per se.

For 3. try an expression like this :

=only({<Ocean= {"=Rank(sum(Amount))=1"}>} Ocean)

Do compare it to your expression.  Get rid of the dollar expansion and always make you close the same amount of brackets as you open.

=only({<Ocean =  {"$(=Rank(sum(Amount),2) = 1))"}>} Ocean)

++++++++++++++++++++++++++++++++++++

As an aside one could use the Aggr() function instead, although I think the aim of the tutorial is to learn Set Analysis, not Aggr().

     =only(if(aggr(Rank(Sum([Amount])),Ocean)=1,Ocean))

++++++++++++++++++++++++++++++++++++

Best wishes as you progress with 5. & onwards.

[swuehl]

It's probably most fun if you manage to solve the tasks yourself, so just giving two hints:

1) If you remove the label from a table chart expression, then hover with the mouse of the expression column header, you will see the expression with all dollar sign expansions expanded.

Try to do this on table for task #5.

How does the set modifier look like? Does it look like a date formatted the same like your Discovered dates?

2) Task #6:

Where can you put a set expression in?

A Primer on Set Analysis

[ruanhaese]

Thanks everyone for your help on this!

Will work through all the responses - really been struggling with it.

"As an aside one could use the Aggr() function instead, although I think the aim of the tutorial is to learn Set Analysis, not Aggr()."

Thanks, I appreciate receiving more than 1 answer, only helps me learn more

"That looks like a good tutorial - never seen it before, but not sure it is a "beginners" tutorial per se."

Regarding the comment above - I made up this tutorial, it wasnt downloaded from anywhere.

Very glad to hear that this isnt a "beginners" tutorial 

[ruanhaese]

saran7de wrote:

Please find attached. I tried my best. !

Wow, Thanks!

Number 9 was a bit of a shortcut though  

as it would not disregard the filters, but other than that very helpful thank you.

[Anonymous]

ruanhaese

Glad you are on the road to becoming a true Qlikie.

Best wishes for your journey with Qlik.

[ruanhaese]

Heres a slightly more polished version attached,

thanks to all the help above.

[Saravanan_Desingh]

May be you can also add two more Quiz to it:

Display All data Excluding Atlantic Ocean

=ONLY({<Ocean-={'Atlantic'}>} Ocean)

Display Data, if either Ocean <> Atlantic OR Fish not start with B

=ONLY({<Ocean-={'Atlantic'}>+<Fish={"B*"}>} Ocean)