Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Data from OLEDB Connection - Adding Columns

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:

IDCountryLast ConnectionLast Connection (New Column)
ID1Country111:00:29 14/8/2017Less than 3 days
ID2Country112:05:11 10/8/20173 - 7 days
ID3Country208:33:21 8/7/2017More than 7 days
ID4Country303:12:34 14/8/2017Less 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!

1 Solution

Accepted Solutions
Highlighted
Master III
Master III

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;

View solution in original post

4 Replies
Highlighted

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


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)
Highlighted
Master III
Master III

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

Highlighted
Master III
Master III

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;

View solution in original post

Highlighted
Not applicable

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!