Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)
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)
Thank you very much Nicole