Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

alvinford
New Contributor III

Sort Inline Table

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

7 Replies
alvinford
New Contributor III

Sort Inline Table

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

sridhar240784
Contributor III

Sort Inline Table

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
New Contributor III

Sort Inline Table

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
New Contributor III

Re: Sort Inline Table

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
Contributor III

Sort Inline Table

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
New Contributor III

Re: Sort Inline Table

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
Honored Contributor

Re: Sort Inline Table

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