topic Re: SQL-QlikView Editor and Case Statement in QlikView

SQL-QlikView Editor and Case Statement

Anonymous — Fri, 20 Nov 2020 18:02:30 GMT

Hello Friends,

First I want to ask if there is any free editor you recommend that can convert my SQL scripts to QlikView. I am very used to SQL and while I learn QlikView, I was thinking at the beginning if I can just convert my SQL scripts to Qlikview. Using an editor it will help me understand how QlikView works.

Second, can someone please give me some good tutorial or links or sample code to how SQL Case Statement works in QlikView? I have an example below if someone can create a sample QlikView code for me to understand.

Let's say I have the below table information and I need to count number of customers that fits in different categories:

Table Name: Customers

Column 1: Products - values iPad, Notebook, iPhone, and iPod

Column 2: Categories - values White, Black,Silver, and Red

	White	Silver	Red
iPad	Count(CustomerID)	Count(CustomerID)	Count(CustomerID)
iPhone	Count(CustomerID)	Count(CustomerID)	Count(CustomerID)
iPod	Count(CustomerID)	Count(CustomerID)	Count(CustomerID)

Please note that I am not including product "Notebook" and category "Black".

I will really appreciate if someone can create a sample QlikView code for this example.

Thank you.

Re: SQL-QlikView Editor and Case Statement

oknotsen — Mon, 06 Apr 2015 11:22:38 GMT

What you seem to be asking for is basic QlikView training.

I would suggest you take a look here and see if you can find training in your language or direct environment:

Find Training | Qlik

If not listed, contact a regional training manager and I am sure you can work out what is the training for you .

Training Contacts | Qlik

Back to the question you are asking: I would just build a chart for that with Categories as the Dimension and count(distinct CustomerID) as the expression.

Re: SQL-QlikView Editor and Case Statement

Anonymous — Mon, 06 Apr 2015 17:51:00 GMT

Hi,

My main concern in the example is how can I get the product values (iPad,iPhone,iPod) on the first column and Category values (White,Silver,Red) in to the first row. And, what if I don't want to display few values like product with color Black. Or, don't want to display Notebook product. I will definitely go through the training to learn more about this example but if you can give me some ideas it will save me some time.

Thanks.

Re: SQL-QlikView Editor and Case Statement

oknotsen — Mon, 06 Apr 2015 20:24:10 GMT

Hi,

If I understand you correct, than what you could do is add both Products and Category as Dimension in a Pivot table in which you drag Category to the top. The expression would still be the same count(distinct CustomerID).

You could of course combine this with a List Box for Products in which you can select the values; only those in selection would be shown.

Alternatively, if you always have a few products (or categories for that matter) which you want to exclude, is use a Set Analysis statement. Here is an example in which product Notebook gets excluded:

count({ < Products -= {Notebook} > } distinct CustomerID)

Re: SQL-QlikView Editor and Case Statement

Anonymous — Mon, 06 Apr 2015 20:37:59 GMT

Yeah, I was thinking about the pivot table and I saw a tutorial previously where a field was just dragged and it changed from column to row. Will try it and update you on how it goes. Thanks.

Re: SQL-QlikView Editor and Case Statement

oknotsen — Mon, 06 Apr 2015 20:42:35 GMT

Good luck .

Re: SQL-QlikView Editor and Case Statement

Anonymous — Mon, 06 Apr 2015 23:35:37 GMT

So I have my pivot table now but there are extra values and was wondering if there is any way we can not include some values/categories in the pivot table. For example, I do not want to include D, G, J, L and number 3, 5, and 6 in the below example. Consider A-L and 1-6 are columns values.

A B C D E F G H I J K L

Re: SQL-QlikView Editor and Case Statement

Anonymous — Tue, 07 Apr 2015 00:15:53 GMT

I got it. I just edited the WHERE clause that included only the values that I wanted to see in the pivot table.

Eg. WHERE Categories IN ('White','Silver','Red')

Re: SQL-QlikView Editor and Case Statement

oknotsen — Tue, 07 Apr 2015 08:16:25 GMT

Downside of that is that the data never gets loaded into QlikView and as a result you will never be able to report / do analysis on it.

So from a BI point of view, I am not so sure if that is the best solution, but I don't know about the underlying customer data and requirements of course.