Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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:
ID | Country | Last Connection | Last Connection (New Column) |
---|---|---|---|
ID1 | Country1 | 11:00:29 14/8/2017 | Less than 3 days |
ID2 | Country1 | 12:05:11 10/8/2017 | 3 - 7 days |
ID3 | Country2 | 08:33:21 8/7/2017 | More than 7 days |
ID4 | Country3 | 03:12:34 14/8/2017 | 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!
You could also use this Expression in Script to insert new Column, as Preceding Load
LOAD *,
If(Today()-Floor(Date(Date#([Last Connection],'hh:mm:ss DD/MM/YYYY'))) < 3,'Less than 3 days',
If(Today()-Floor(Date(Date#([Last Connection],'hh:mm:ss DD/MM/YYYY'))) > 7,'More than 7 days',
'3 - 7 days')) as NewColumn;
SQL Select * From Table;
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?
Sample:
Load * From <Source>;
Concatenate(Sample)
Load *, If([Last Connection] <= Today()-3, [Last Connection]) as Flag, 'Less than 3 days' as FlagFilter Resident Sample where ID = 'ID1';
Concatenate(Sample)
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';
Concatenate(Sample)
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
Hi Jonathan,
in front end, Expression
If(Today()-Floor(Date(Date#([Last Connection],'hh:mm:ss DD/MM/YYYY'))) < 3,'Less than 3 days',
If(Today()-Floor(Date(Date#([Last Connection],'hh:mm:ss DD/MM/YYYY'))) > 7,'More than 7 days',
'3 - 7 days'))
Regards,
Antonio
You could also use this Expression in Script to insert new Column, as Preceding Load
LOAD *,
If(Today()-Floor(Date(Date#([Last Connection],'hh:mm:ss DD/MM/YYYY'))) < 3,'Less than 3 days',
If(Today()-Floor(Date(Date#([Last Connection],'hh:mm:ss DD/MM/YYYY'))) > 7,'More than 7 days',
'3 - 7 days')) as NewColumn;
SQL Select * From Table;
Thanks Antonio,
The formula had to be tweaked according to the data, but I managed to get it work by using the LOAD function in the script instead of creating a new list box with an expression.
Many thanks all!