9 Replies Latest reply: Feb 7, 2017 7:04 AM by Sunny Talwar RSS

    Order By Month

    RANJIT KAKADE

      Hi Community,

      I have to sort out the data by month wise,

      for same purpose I written a script-code as below:

       

      //compute the month-wise brokerage

      Brokerage_QLIK:

      LOAD Left(trade_date,6) as fiscal_month,

      sum(brokerage) as Total_Brokerage

      Resident Brokerage

      Group by  Left(trade_date,6)

      Order by 1;

       

      But My Output data in Qliksense is not appearing as per required Order:PFA more details and help me please

        • Re: Order By Month
          RANJIT KAKADE

          I have to present the monthdata by ascending order

          • Re: Order By Month
            Sunny Talwar

            How about you try this:

             

            Brokerage_QLIK:

            LOAD fiscal_month,

                 sum(brokerage) as Total_Brokerage

            Group by fiscal_month;

            LOAD Date(Date#(Left(trade_date, 6), 'YYYYMM'), 'YYYYMM') as fiscal_month,

                 brokerage

            Resident Brokerage;

              • Re: Order By Month
                RANJIT KAKADE

                As per your script,No changes in the data presentation

                  • Re: Order By Month
                    Sunny Talwar

                    Did you sort by fiscal_month in your chart?

                      • Re: Order By Month
                        RANJIT KAKADE

                        No, using this script for data reconcillation purpose in Log file of qliksense, I have checked it by data reloading at the data model viewer using table preview mode

                        • Re: Order By Month
                          RANJIT KAKADE

                          My whole script for the same for Qlik_data



                          $(Must_Include='lib://***************\DataPath\PathFile.txt');

                           

                          2

                           

                          LET vQVD_Path = '$(vQvd_path_ODS_Transactions)';

                           

                          3

                           

                          LET vQVD_Name = 'PLFMWISE_BRKG_TURNOVR';

                           

                          4

                           

                           

                          5

                           

                          //ENTER EITHER vNoOfYears OR vFrom_Fiscal_Yr;

                           

                          6

                           

                          LET vNoOfYears = 1;   //INPUT SHOULD BE > 0 eg: 3 TO GET LAST 3 FISCAL YRS + 1 CURRENT FISCAL YR

                           

                          7

                           

                          LET vFrom_Fiscal_Yr = 'NONE'; // INPUT FORMAT eg: 'FY 2013' GETS DATA FROM FISCAL YR 2013 TO TILL NOW[2017 FISCAL YR]

                           

                          8

                           

                          LET vTable_Name = 'Brokerage';

                           

                          9

                           

                           

                          10

                           

                          $(Must_Include='*************************.qvs');

                           

                          11

                           

                           

                          12

                           

                          //brokerage summary for min date, max date & total brokerage

                           

                          13

                           

                          BrokerageSummary: 

                           

                          14

                           

                          Load 

                           

                          15

                           

                          Min(trade_date) as Min_Date,

                           

                          16

                           

                          Max(trade_date) as Max_Date,

                           

                          17

                           

                          Sum(brokerage) as Total_Brokerage

                           

                          18

                           

                          Resident Brokerage;

                           

                          19

                           

                           

                          20

                           

                           

                          21

                           

                          //store the summary data into variables

                           

                          22

                           

                          LET vFrom_Date = Peek('Min_Date',0,'BrokerageSummary');

                           

                          23

                           

                          LET vTo_Date = Peek('Max_Date',0,'BrokerageSummary');

                           

                          24

                           

                          Let vTotal_Brokerage= Peek('Total_Brokerage',0,'BrokerageSummary');

                           

                          25

                           

                           

                          26

                           

                          //drop the summary table

                           

                          27

                           

                          drop table BrokerageSummary;

                           

                          28

                           

                           

                          29

                           

                           

                          30

                           

                           

                          31

                           

                          Brokerage_QLIK:

                           

                          32

                           

                          LOAD fiscal_month,

                           

                          33

                           

                             sum(brokerage) as Total_Brokerage

                           

                          34

                           

                          Group by fiscal_month;

                           

                          35

                           

                          LOAD Date(Date#(Left(trade_date, 6), 'YYYYMM'), 'YYYYMM') as fiscal_month,

                           

                          36

                           

                             brokerage

                           

                          37

                           

                          Resident Brokerage;

                           

                          38

                           

                           

                          39

                           

                           

                          40

                           

                          //drop unwanted tables

                           

                          41

                           

                          drop table Brokerage;