topic Re: Order By Month in App Development

Order By Month

kakaderanjit53 — Tue, 07 Feb 2017 10:22:08 GMT

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

kakaderanjit53 — Tue, 07 Feb 2017 10:23:27 GMT

I have to present the monthdata by ascending order

Re: Order By Month

sunny_talwar — Tue, 07 Feb 2017 10:27:22 GMT

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

kakaderanjit53 — Tue, 07 Feb 2017 10:41:48 GMT

As per your script,No changes in the data presentation

Re: Order By Month

sunny_talwar — Tue, 07 Feb 2017 10:42:26 GMT

Did you sort by fiscal_month in your chart?

Re: Order By Month

kakaderanjit53 — Tue, 07 Feb 2017 10:51:29 GMT

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

kakaderanjit53 — Tue, 07 Feb 2017 10:55:48 GMT

My whole script for the same for Qlik_data 




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

LET vQVD_Path = '$(vQvd_path_ODS_Transactions)';

LET vQVD_Name = 'PLFMWISE_BRKG_TURNOVR';

//ENTER EITHER vNoOfYears OR vFrom_Fiscal_Yr;

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

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

LET vTable_Name = 'Brokerage';

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

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

BrokerageSummary:

Load

Min(trade_date) as Min_Date,

Max(trade_date) as Max_Date,

Sum(brokerage) as Total_Brokerage

Resident Brokerage;

//store the summary data into variables

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

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

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

//drop the summary table

drop table BrokerageSummary;

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;

//drop unwanted tables

drop table Brokerage;

Re: Order By Month

sunny_talwar — Tue, 07 Feb 2017 10:59:50 GMT

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;

Re: Order By Month

kakaderanjit53 — Tue, 07 Feb 2017 11:13:17 GMT

Hi Sunny,

Thanks alot.,

your script given me correct result.

Re: Order By Month

sunny_talwar — Tue, 07 Feb 2017 12:04:27 GMT

Super