topic Re: To Control Sorting while Loading Data i.e in the Load Statement in QlikView

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

shannoypaul — Wed, 25 Nov 2020 16:16:04 GMT

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

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

marcus_malinow — Thu, 13 Dec 2018 11:17:17 GMT

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

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

mohan_1105 — Thu, 13 Dec 2018 11:22:00 GMT

Hi Paul,

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

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

sunny_talwar — Thu, 13 Dec 2018 12:05:22 GMT

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?

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

shannoypaul — Thu, 13 Dec 2018 19:12:57 GMT

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

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

sunny_talwar — Thu, 13 Dec 2018 19:22:26 GMT

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

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

shannoypaul — Thu, 13 Dec 2018 22:39:17 GMT

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.

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

sunny_talwar — Fri, 14 Dec 2018 01:48:43 GMT

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?

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

shannoypaul — Fri, 14 Dec 2018 02:59:13 GMT

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

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

shannoypaul — Fri, 14 Dec 2018 03:01:55 GMT

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.

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

sunny_talwar — Fri, 14 Dec 2018 12:09:42 GMT

Something like this

Table:
LOAD * INLINE [
    Key, Created Date, Flag, 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
];

FinalTable:
LOAD *,
	 0 as New_Value
Resident Table
Where Flag = 0;

Concatenate (FinalTable)
LOAD *,
	 If(Key = Previous(Key), Value - Peek('Value'), 0) as New_Value;
LOAD *
Resident Table
Where Flag = 1
Order By Key, [Created Date];

DROP Table Table;