Data from OLEDB Connection - Adding Columns

[#1]

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!

[Anil_Babu_Samineni]

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

[antoniotiman]

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

[antoniotiman · Accepted solution]

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;

[#5]

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!