Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
I have to present the monthdata by ascending order
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;
As per your script,No changes in the data presentation
Did you sort by fiscal_month in your chart?
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
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;
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;
Hi Sunny,
Thanks alot.,
your script given me correct result.
Super