Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
star_limit
Creator II
Creator II

SQL-QlikView Editor and Case Statement

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

WhiteSilverRed
iPadCount(CustomerID)Count(CustomerID)Count(CustomerID)
iPhoneCount(CustomerID)Count(CustomerID)Count(CustomerID)
iPodCount(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.

1 Solution

Accepted Solutions
star_limit
Creator II
Creator II
Author

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

View solution in original post

8 Replies
oknotsen
Master III
Master III

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.

May you live in interesting times!
star_limit
Creator II
Creator II
Author

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
Master III
Master III

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)

May you live in interesting times!
star_limit
Creator II
Creator II
Author

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
Master III
Master III

Good luck .

May you live in interesting times!
star_limit
Creator II
Creator II
Author

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

star_limit
Creator II
Creator II
Author

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
Master III
Master III

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.

May you live in interesting times!