Order By Month

[kakaderanjit53]

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

[kakaderanjit53]

I have to present the monthdata by ascending order

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

[kakaderanjit53]

As per your script,No changes in the data presentation

[sunny_talwar]

Did you sort by fiscal_month in your chart?

[kakaderanjit53]

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

[kakaderanjit53]

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;

[sunny_talwar · Accepted solution]

Does this help?

Brokerage_QLIK_TEMP:

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

     brokerage

Resident Brokerage;

Brokerage_QLIK:

LOAD fiscal_month1 as fiscal_month,

     sum(brokerage) as Total_Brokerage

Resident Brokerage_QLIK_TEMP

Group by fiscal_month1

Order By fiscal_month1;

DROP Table Brokerage_QLIK_TEMP;

[kakaderanjit53]

Hi Sunny,

Thanks alot.,

your script given me correct result.

[sunny_talwar]

Super