SQL-QlikView Editor and Case Statement

[Anonymous]

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.

[oknotsen]

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.

[Anonymous]

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.

[oknotsen]

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)

[Anonymous]

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.

[oknotsen]

Good luck .

[Anonymous]

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

1

2

3

4

5

6

[Anonymous · Accepted solution]

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')

[oknotsen]

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.