Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mibb1234
Contributor II
Contributor II

Car DB - displaying new features by year/model

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)

ModelYearFeature
VW Passat2018Bluetooth
VW Passat2018CD Player
VW Passat2018Heated Seats
VW Passat2018Remote Start
BMW 3202018Bluetooth
BMW 3202018CD Player
BMW 3202018Heated Seats
BMW 3202019Bluetooth
BMW 3202019CD Player
BMW 3202019Heated Seats
BMW 3202019Remote Start
BMW 3202019Xenon Headlights

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

1 Solution

Accepted Solutions
Nicole-Smith

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)

View solution in original post

2 Replies
Nicole-Smith

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)

mibb1234
Contributor II
Contributor II
Author

Thank you very much Nicole