topic Re: Comparing data from one table in QlikView

Comparing data from one table

Anonymous — Tue, 28 Jul 2015 23:42:12 GMT

Hi All,

I will start with my data model, I only have one table, Transactions, and the Master Calendar,

Transactions:

Load

event_id,

previous_event_id,

trans_id,

barcode,

item_id,

item_type,

location,

operator,

event_day,

Hour(timestamp) as hour,

timestamp

From

(qvd);

This table contains all the transactions from each event, every year the event might reoccur, but it is never on the same date, that is why I count each day of the event in the event_day field (1, 2, 3, 4...).

I want to create three charts: 1. dimension: hour, expression: count({<item_type={'P'}>}item_id)

2. dimension: location, expression: count({<item_type={'P'}>}item_id)

3. dimension: operator, expression: count({<item_type={'P'}>}item_id)

To give you an example:

2014: event_id = 258

2015: event_id = 374, previous_event_id = 258

I did get it to kind of work, I did a resident load from Transactions table:

Transactions_previous:

Load

event_id as previous_event_id,

trans_id as trans_id_prev,

barcode as barcode_prev,

item_id as item_id_prev,

item_type as item_type_prev,

location as location_prev,

operator as operator_prev,

event_day,

hour,

timestamp as timestamp_prev

Resident Transactions;

This creates a synthetic key:

$Syn 1 Table:

previous_event_id,

event_day,

hour;

I only created the hourly comparison and ran into an issue

Looks really good, but in 2014 there were transactions after 20:00 pm, so it only took the hours from the selected event_id.

this is the expression that I used for 2015: count({<item_type={'P'}>}item_id)

for 2014: count({<item_type_prev={'P'}>}item_id_prev)

Does anyone have any suggestions?

Message was edited by: Smaranda Fetila I added some sample data (Transactions.xlsx) and my application (Event_Transactions.qvw) and the second application showing what I tried doing with the resident load (Event_Transactions - Resident Load.qvw), any help would be appreciated! I don't have much experience with QlikView, if I did something wrong please let me know.

Message was edited by: Smaranda Fetila Also the end result is showed in the application with some images.

Re: Comparing data from one table

sunny_talwar — Wed, 29 Jul 2015 01:23:30 GMT

Can you provide some sample data to work with and the expected result???

Re: Comparing data from one table

marcus_sommer — Wed, 29 Jul 2015 10:30:23 GMT

You don't need this second resident load. The different years could you fetch per set analysis like:

count({< year = {2015}>} item_id)

count({< year = {2014}>} item_id)

or more dynamically

count({< year = {"$(=max(year))"}>} item_id) // current year based on selections

count({< year = {"$(=max(year)-1)"}>} item_id) // previous year

Further I suggest to remove the timestamp and split it into two fields like:

date(floor(timestamp)) as date,

time(frac(timestamp)) as time

and to use a Master-Time-Table - see here for more informations about date-fields:

How to use - Master-Calendar and Date-Values

- Marcus

Re: Comparing data from one table

ankitbisht01 — Wed, 29 Jul 2015 10:40:44 GMT

Please provide sample data and desired output you want

Regards

Ankit Bisht

Re: Comparing data from one table

sasiparupudi1 — Wed, 29 Jul 2015 11:11:54 GMT

any reason for not renaming

event_day,

hour fields?

I think you only need to keep them the same if you want to join both the tables otherwise rename them . that shouuld get rid of the synthetic key issue and hopefully give you your results..

It will be really great, if you could attach a sample so that people could come up with a good solution to your issue

HTH

Sasi

Re: Comparing data from one table

Anonymous — Wed, 29 Jul 2015 21:52:08 GMT

Marcus,

I tried what you recommended, but when I select the event_id that I want to see it basically compares the data from this year to the same data. Don't I have to differentiate it somehow?

I attached some sample data and the application, could you take a look at it?

Re: Comparing data from one table

Anonymous — Wed, 29 Jul 2015 21:55:12 GMT

For the hourly comparison I need all three fields in common. Without the synthetic key it wouldn't compare the right data, would it? I attached some sample data and the app, could you take a look?

Thank you,

Smaranda

Re: Comparing data from one table

Anonymous — Wed, 29 Jul 2015 21:56:40 GMT

I added some sample data, the application and the end expected result in the application.

Re: Comparing data from one table

Anonymous — Wed, 29 Jul 2015 21:56:52 GMT

I added some sample data, the application and the end expected result in the application.

Re: Comparing data from one table

marcus_sommer — Thu, 30 Jul 2015 06:12:01 GMT

Actually I couldn't look into your examples but I think you have with your one table everything what you need to make your calculations. I suggest you have a look on what set analysis is and how it worked - it's really powerful:

A Primer on Set Analysis

Set Analysis: syntaxes, examples

Further you should definitely avoid synthetic keys - see why and how you get started with qlik datamodels:

Get started with developing qlik datamodels

- Marcus