To Control Sorting while Loading Data i.e in the Load Statement

[shannoypaul]

I am using a Previous() function for computing variance. The computation is dependent on the sorting sequence of the data being Loaded. The order by clause is not yielding the correct results as it is sorting it the right way on the o/p and not while loading the data. TIA, Shannoy

[marcus_malinow]

Hi,

a bit more information would be useful. Maybe a code snippet?

A note - if you are loading from a qvd, load first into a temporary table, then do a resident load with your sort order applied.

Marcus

[mohan_1105]

Hi Paul,

Try by taking a resident table and sort the values. Use the order by statement ASC or DESC.

[sunny_talwar]

Where exactly are you pulling your data from? and what do you mean when you say this "it is sorting it the right way on the o/p and not while loading the data"? Sorting the right way on the o/p? What is o/p here? front end?

[shannoypaul]

I am pulling from a qvd.

I will break it down to 2 parts

1. Current Setup

SortedTable:

Load Key, Created Date, Value, Flag From ResidentTable Order by Key, Created Date, Flag asc

Store SortedTable in Sorted.qvd

ComputeTable:

Load *,

           Value - Previous(Value) as Compute

From Sorted.qvd

The catch is Previous(Value)  can have 2 possibilities i.e. Flag can be 0 and/or 1.

2. Required Approach:

The Previous(Value) to be considered should have Flag =1 for cases where there are 2 options 

a. Flag  for the previous row is 0 or 1 - No Problem as this is a distinct value

b Flag is 0 and 1 - Consider previous row with flag = 1

** Hence asc ordering done but 

ComputeTable data when checked in the UI is sorted correctly (Flag asc)  but the Compute value is of the record for Flag = 0.

Hence I inferred that the internal loading of a table is not affected by order by clause

[sunny_talwar]

Are you saying that Key and Created Date can repeat with Flag = 0 and Flag = 1? and you only want to pick Previous(Value) if Previous(Flag) = 1?

Question, can you have max 2 rows for a combination of Key and Created Date or can you have 3+ rows like this

Key Created Date Flag Value
123 12/13/2018 0 10
123 12/13/2018 0 20
123 12/13/2018 1 30
123 12/13/2018 1 25

[shannoypaul]

Yes.

3+ rows however I have got it to only 2 cases by grouping it with the key "Created Date,Key, Flag"

Key Created Date Flag Avg. Value

123 12/13/2018 0 15
123 12/13/2018 1 27.5
123 12/14/2018 1 30                            (Can be an averaged value or a single line)
For the 3rd Row I would want to compute 30-27.5 by using the previous() Function.

[sunny_talwar]

But what happens if your data is like this
123 12/13/2018 0 15
123 12/13/2018 1 27.5
123 12/14/2018 0 28
123 12/14/2018 1 30

or once changed to 1, it will never become 0 again?

[shannoypaul]

I have put a check to compare the Key and the Created Date, if the Key and Created Date is same it will iterate to the Previous(Previous(Value)):

123 12/13/2018 0 15
123 12/13/2018 1 27.5
123 12/14/2018 0 28
123 12/14/2018 1 30

Or in this case you can assume that for Flag = 0 we need not do any computation where as for Flag = 1

30 - 27.5 i .e Row(4) - Row(2) because Row(2) has Flag = 1

[shannoypaul]

All checks are in place the only hurdle is to pick the previous row with Flag=1 for computation 🙂

Thanks for asking!

P.S. @sunny_talwar, you were the one who helped me build that but it was on a different thread.