topic Re: Beginners Tutorial in QlikView

Beginners Tutorial

ruanhaese — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Beginners Tutorial

Saravanan_Desingh — Wed, 02 Mar 2016 21:16:13 GMT

Please find attached. I tried my best. !

Re: Beginners Tutorial

Anonymous — Wed, 02 Mar 2016 21:19:05 GMT

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.

Re: Beginners Tutorial

swuehl — Wed, 02 Mar 2016 22:02:06 GMT

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

Re: Beginners Tutorial

ruanhaese — Thu, 03 Mar 2016 04:37:19 GMT

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

Re: Beginners Tutorial

ruanhaese — Thu, 03 Mar 2016 04:40:38 GMT

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.

Re: Beginners Tutorial

Anonymous — Thu, 03 Mar 2016 13:01:00 GMT

ruanhaese

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

Best wishes for your journey with Qlik.

Re: Beginners Tutorial

ruanhaese — Tue, 08 Mar 2016 05:17:49 GMT

Heres a slightly more polished version attached,

thanks to all the help above.

Re: Beginners Tutorial

Saravanan_Desingh — Tue, 08 Mar 2016 20:39:57 GMT

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)