I'm quite new to Qlikview - and was trying to make certain charts with some data I have. Some background about the issue I'm facing:
I am drawing my data from an Oracle Database using an OLEDB connection in the Edit Scripts area. Examples of what data I draw would be IDs of individuals, countries they are from, and when their last connection to a particular server was.
Is it possible for me to add a column to this extracted database, which involves calculation of the current time minus their last connection date? An example would be as below:
Last Connection (New Column)
Less than 3 days
3 - 7 days
More than 7 days
Less than 3 days
Instead of showing the exact date, I would like to add a column which is able to classify each of these times. I was able to create a new List Box with an expression using formulas to allow me to filter these results like the way I wanted in the fourth column, but I was unable to add this data into the Quick Chart Wizard as it was not part of any Tables, or was not recognized as a field.
Is there a proper way to insert a column to data acquired in this way (via OLEDB connections)? Any help would be much appreciated, thank you!
As always, I would recommend to do aggregation or calculation in DB engine itself due to save the time and performance. And, Here I am not sure whether we followed you on single basis. But, Some analysis i was thinking this? Does this helps you?
Load * From <Source>;
Load *, If([Last Connection] <= Today()-3, [Last Connection]) as Flag, 'Less than 3 days' as FlagFilter Resident Sample where ID = 'ID1';
Load *, If([Last Connection] >= Today()-3 and [Last Connection] <= Today()-7, [Last Connection]) as Flag, '3-7 days' as FlagFilter Resident Sample where ID = 'ID2';
Load *, If([Last Connection] >= Today()-7, [Last Connection]) as Flag, 'More than 7 days' as FlagFilter Resident Sample where ID = 'ID3';
In future, you can use FlagFilter for restriction with in the set analysis, If required
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)