topic Re: Sort Inline Table in QlikView

Sort Inline Table

alvinford — Tue, 13 Dec 2011 02:27:32 GMT

Hi Friends,

I am unable to sort the below inline statement . Request your help to do that. Want to sort it based on the MonthNo but could not do that.

Month1:

load * inline [

Month,MonthNo

Jan,1
Feb,2
May,5
Jun,6
Jul,7
Aug,8
Mar,3
Apr,4
Sep,9
Oct,10
Nov,11
Dec,12
];

Sort Inline Table

alvinford — Tue, 13 Dec 2011 02:31:59 GMT

Hey I want to do it at the Script Level using the Order By Command.

Sort Inline Table

sridhar240784 — Tue, 13 Dec 2011 02:45:34 GMT

Hi,

Check below code helps you or not.

Month1:

Load Dual(Month,MonthNo) as Month,MonthNo;

load * inline [

Month,MonthNo

Jan,1

Feb,2

May,5

Jun,6

Jul,7

Aug,8

Mar,3

Apr,4

Sep,9

Oct,10

Nov,11

Dec,12

];

-Sridhar

Sort Inline Table

alvinford — Tue, 13 Dec 2011 02:59:28 GMT

Hi Sridhar,

Thanks for your reply.

If I am using the Dual function and tying to left join the table with month as the key field the values are duplicating.

Can you suggest some other option..

Regards,

Alvin.

Re: Sort Inline Table

alvinford — Tue, 13 Dec 2011 02:59:49 GMT

Hi Sridhar,

Thanks for your reply.

If I am using the Dual function and tying to left join the table with month as the key field the values are duplicating.

Can you suggest some other option..

Regards,

Alvin.

Sort Inline Table

sridhar240784 — Tue, 13 Dec 2011 04:26:09 GMT

Hi Alvin,

I hope the key field which you are trying to join is string field and the inline month is the Number.

Are you extracting the month from a date field in you tarnsaction table?

Could pls post a sample, so that it would be easy for us to give you the solution.

-Sridhar

Re: Sort Inline Table

alvinford — Tue, 13 Dec 2011 07:11:08 GMT

Hiii All,

Please find the attched QVD. It has the following columns

LOAD

PRVT_STAFF_ID,

MTH_YR,

Month,

Year,

FACT_STAFF_LV1:

LOAD PRVT_STAFF_ID,

MTH_YR,

Month,

if(Month='Jan',1,

if(Month='Feb',2,

if(Month='Mar',3,

if(Month='Apr',4,

if(Month='May',5,

if(Month='Jun',6,

if(Month='Jul',7,

if(Month='Aug',8,

if(Month='Sep',9,

if(Month='Oct',10,

if(Month='Nov',11,12))))))))))) as MonthNO,

Year,

LV_TAKEN_CNT,

LV_TYPE_SK,

// LAST_UPD_DATE

FROM

C:\Users\Desktop\FACT_STAFF_LV1.Qvd

(qvd);

the problem is its not sorting as expected . Can any one help me to sort and achieve the cummulative result for the column LV_TAKEN_CNT.Plase ignore last_upd_date column.The main key field is PRVT_STAFF_ID.

Regards,

Alvin.

LV_TAKEN_CNT,

LV_TYPE_SK,

FROM

I want to calculate the Cummulative Values for the column LV_TAKEN_CNT so inorder to sort it i have created the following script.

D:\Users\FACT_LV.Qvd

(qvd);

Re: Sort Inline Table

Sokkorn — Tue, 13 Dec 2011 07:12:37 GMT

Hi alvinford,

Why you don't arrange it in load script?

load * inline [
Months,MonthNo
Jan,1
Feb,2
Mar,3
Apr,4
May,5
Jun,6
Jul,7
Aug,8
Sep,9
Oct,10
Nov,11
Dec,12
];

If you want to sort by [Months] then try this =Match(Months, $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39)))

Regards,

Sokkorn Cheav