topic Car DB - displaying new features by year/model in QlikView

Car DB - displaying new features by year/model

mibb1234 — Mon, 15 Oct 2018 14:42:09 GMT

Hello,

Apologies if this is very basic, I am quite new and just getting to grips with QlikView. I just need a bit of help or direction.

Below is an example of some of the data. What I'm looking to build is a list of new features by model+year that is unique to that model only.

The desired output would be in a table with Year, Model & New Feature with the ability of the user to select Model and Year to narrow down the results.

From the sample data set below I'd expect to see

BMW 320 - 2019 - Remote Start (New in 2019 for BMW 320. Even though VW Passat 2018 has this feature the search scope should only be BMW 320 & all previous model years
BMW 320 - 2019 - Xenon Headlights

The search scope should only look backwards. e.g. if the user selected BMW 320 2018 it should not list new features in 2019 but only look at previous years to see if it's new in 2018

I've had a search around the forums and found expressions relating to new customers. I've been trying to adapt them to this scenario but without luck. Am I heading in the right direction or should I focus on the load script for this?

I've tried variations of the below but I'm still not sure how to output each new feature into a table and ensure it's tied to a model and that it checks previous years only.

=count({<Feature=E({<Year={'<$(=max(Year))'}>}Feature)>} distinct Feature)

Model	Year	Feature
VW Passat	2018	Bluetooth
VW Passat	2018	CD Player
VW Passat	2018	Heated Seats
VW Passat	2018	Remote Start
BMW 320	2018	Bluetooth
BMW 320	2018	CD Player
BMW 320	2018	Heated Seats
BMW 320	2019	Bluetooth
BMW 320	2019	CD Player
BMW 320	2019	Heated Seats
BMW 320	2019	Remote Start
BMW 320	2019	Xenon Headlights

Again sorry if this is very basic, any help or tips are much appreciated.

Re: Car DB - displaying new features by year/model

Nicole-Smith — Mon, 15 Oct 2018 16:20:56 GMT

I would flag your Features in the load script with something like this:

Data:

LOAD * INLINE [

Model, Year, Feature

VW Passat, 2018, Bluetooth

VW Passat, 2018, CD Player

VW Passat, 2018, Heated Seats

VW Passat, 2018, Remote Start

BMW 320, 2018, Bluetooth

BMW 320, 2018, CD Player

BMW 320, 2018, Heated Seats

BMW 320, 2019, Bluetooth

BMW 320, 2019, CD Player

BMW 320, 2019, Heated Seats

BMW 320, 2019, Remote Start

BMW 320, 2019, Xenon Headlights

];

/* Flagging Old Features */

LEFT JOIN (Data)

LOAD Model,

Year+1 AS Year,

Feature,

1 AS OldFeatureFlagTemp

RESIDENT Data;

/* Replacing nulls with 0s so we can use in set analysis */

LEFT JOIN (Data)

LOAD Model,

Year,

Feature,

ALT(OldFeatureFlagTemp, 0) AS OldFeatureFlag

RESIDENT Data;

DROP FIELD OldFeatureFlagTemp;

Then you can use an expression like this to get only the new features:

count({<OldFeatureFlag={0}>}DISTINCT Feature)

Re: Car DB - displaying new features by year/model

mibb1234 — Tue, 16 Oct 2018 07:55:23 GMT

Thank you very much Nicole