Sort Inline Table

[alvinford]

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
];

[alvinford]

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

[sridhar240784]

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

[alvinford]

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.

[alvinford]

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.

[sridhar240784]

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

[alvinford]

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);

[Sokkorn]

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