Comparing data from one table

[Anonymous]

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.

[sunny_talwar]

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

[marcus_sommer]

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

[ankitbisht01]

Please provide sample data and desired output you want

Regards

Ankit Bisht

[sasiparupudi1]

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

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[marcus_sommer]

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