4 Replies Latest reply: Aug 18, 2017 1:32 AM by Jonathan Yip RSS

    Data from OLEDB Connection - Adding Columns

    Jonathan Yip

      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!

        • Re: Data from OLEDB Connection - Adding Columns
          Anil Babu

          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


          • Re: Data from OLEDB Connection - Adding Columns
            Antonio Mancini

            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