topic Re: Sorting table in script ? in QlikView

Sorting table in script ?

ganeshreddy — Sun, 17 Jan 2016 20:21:52 GMT

Hi All,

I have a senario where the temporary calender needs to be sorted in desending order of modified date, but i am unable to sort that table in script. Please find attached files.

Note: As i got huge number of records i need to use distinct in that table.

Best Regards,

Ganesh.

Re: Sorting table in script ?

sunny_talwar — Sun, 17 Jan 2016 20:32:06 GMT

May be this:

Delta:

LOAD id,

site,

partionkey,

modifieddate as modifieddatetemp,

sale

FROM

[Modi Test.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

YEAR_MONTH_CALENDAR:

LOAD Distinct

timestamp(Monthstart(partionkey)) as MonthStart,

timestamp(MonthEnd(partionkey)) as MonthEnd,

Year(floor(partionkey))&NUM(Month(floor(partionkey)),'00') AS MonthName,

modifieddatetemp as modifieddate

Resident Delta

Order by modifieddatetemp desc;

FinalDelta:

LOAD id,

site,

partionkey,

modifieddatetemp as modifieddate,

sale

Resident Delta;

DROP Table Delta;

DataViewer is still incorrect sorting, but when you sort the data by load order in table box it shows the right sorting which means that the data is being correctly sorted:

Re: Sorting table in script ?

stevedark — Sun, 17 Jan 2016 20:42:17 GMT

Hi Ganesh,

There are very few cases where sorting in the load script is necessary. Using PEEK or doing an incremental load are the only cases I can think of. In the main sorting in the front end makes much more sense.

Why do you think that sorting in the script is required?

There are some cases where the ORDER BY statement can not be used, loading from QVD for instance. In these cases you can load to a temporary table and sort on the load from RESIDENT, as sunindia‌ suggests.

The downside of this is it can make your load script take twice the time to execute.

This DISTINCT predicate can put put in to any type of load, and will remove all duplicate rows - use with caution though as it can cause duplicate values to go missing - giving incorrect totals. A GROUP BY with SUM statements is usually preferable.

Hope that helps.

Steve

Re: Sorting table in script ?

ganeshreddy — Sun, 17 Jan 2016 20:48:53 GMT

Thanks for the reply sunny,

I need to use this calender table further in script not in UI. Here we are getting DataViewer results only, because if i use peek function as mentioned below it is fetching the wrong record .

peek('modifieddate',-1,'YEAR_MONTH_CALENDAR');

My actual requirement is to sort months in desending order of modified date. Could you please help me out here.

Ganesh.

Re: Sorting table in script ?

stevedark — Sun, 17 Jan 2016 20:58:11 GMT

Hi Ganesh,

I notice you have a semi colon on the end of that statement, is this statement run on it's own after the load.

If you do the sorted resident load, as recommended then this will give you the last modified date.

More efficient would be to load the table in any order then do a resident LOAD with a GROUP BY and a MAX or a MIN.

You only need to sort for a peek if you are referring back to the previous row in a load for example:

ModifiedDate,

ModifiedDate - Peek('ModifiedDate', -1) as DaysSincePriorChange,

Hope that helps,

Steve

Re: Sorting table in script ?

ganeshreddy — Sun, 17 Jan 2016 21:14:25 GMT

Hi Steve,

Yes your assumption is correct, i am doing incrimental load with that calender table.

Here sorting in the script is required , because it is a monthly QVD updation is there futher in script. Please find metioned pseudo code.

Delta:

LOAD id,

site,

partionkey,

modifieddate,

sale

FROM

[Modi Test.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

YEAR_MONTH_CALENDAR:

LOAD

distinct

timestamp(Monthstart(partionkey)) as MonthStart,

timestamp(MonthEnd(partionkey)) as MonthEnd,

Year(floor(partionkey))&NUM(Month(floor(partionkey)),'00') AS MonthName,

modifieddate

Resident Delta Order by modifieddate desc;

Let vCount=NoOfRows('YEAR_MONTH_CALENDAR');

/********************* Loop through each month generated in 'YEAR_MONTH_CALENDAR' table ***************************/

FOR i=1 to $(vCount)

LET vMonth = Peek('MonthName',-$(i),'YEAR_MONTH_CALENDAR');

LET vMonthStart = Peek('MonthStart',-$(i),'YEAR_MONTH_CALENDAR');

LET vMonthEnd = Peek('MonthEnd',-$(i),'YEAR_MONTH_CALENDAR');

LET vTableName = 'Result' & '$(vMonth)' ;

LET vReloadTimeIn = Now();

/********** If QVD for YearMonth already exists Insert/Update to the existing QVD based on primary key, or else move to ELSE part to create new QVD for that month*******/

IF(not IsNull(FileTime('$(vQVDDataDir)\$(vTableName).qvd'))) then

[$(vTableName)]:

NoConcatenate

LOAD

Resident Delta

Where Year(floor(PartitionKey))&NUM(Month(floor(PartitionKey)),'00')='$(vMonth)';

CONCATENATE

LOAD

FROM [$(vQVDDataDir)\$(vTableName).qvd] (qvd)

WHERE NOT EXISTS (ID);

ELSE

[$(vTableName)]:

NoConcatenate

LOAD

Resident Delta

Where Year(floor(PartitionKey))&NUM(Month(floor(PartitionKey)),'00')='$(vMonth)';

Drop table Delta;

In the above script i need to create a calender table with desending order of modified date which i am unable to do so far. Is there any other whay out to sort that table? Could you please help me out here.

Ganesh

Re: Sorting table in script ?

ganeshreddy — Sun, 17 Jan 2016 21:22:29 GMT

Here the reason for soring with modified date is. Some times data base is updating any of previous months records only. It is a rare case as incrimental load with montly QVDs.

Ganesh

Re: Sorting table in script ?

stevedark — Mon, 18 Jan 2016 07:55:03 GMT

Hi Ganesh,

I get the importance of the sorting now, the WHERE EXISTS wanting to see new records first.

The problem you have is that the date is loading from the TXT file, so is inherently not a date, but is a text string. When you sort (and the syntax for ORDER BY is correct in the script above) it is sorting as a string, not as a date.

To solve this you will need to convert the date on load, like this:

Delta:

LOAD id,

site,

partionkey,

Date#(modifieddate, 'YYYY-MM-DD hh:mm:ss') as modifieddate,

sale

FROM

[Modi Test.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Here it is critical that the date format string matches the format of the dates in the text file. You can add a Date function around that code ( Date(Date#( ) to format the output - but if it is just used for sorting this is not important.

I suspect that you also want to do similar to partionkey, if that is also a date.

The easy way to test if that bit of code is working is to load the data from the text file in isolation and do a list box on modifieddate. If it sorts right in a a ListBox then it will sort fine with an ORDER BY from a RESIDENT load.

Hope that helps,

Steve

Re: Sorting table in script ?

ganeshreddy — Wed, 20 Jan 2016 07:26:20 GMT

Hi Steve,

Thanks a lot, i worked with your suggestions. My requirement is fulfilled now. Final work file and data file are attached to this post. PFA

Best Regards,

Ganesh

Re: Sorting table in script ?

stevedark — Wed, 20 Jan 2016 08:07:02 GMT

Glad it's now working. Thanks for sharing the solution.

Steve