Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a daily transaction table that captures the daily orders by country and city.
Data Table:
Output Table:
I am trying create a summarised data table in the load script like the below. Need to sum up the values by month, quarter, year and also for each entity type (country & city).
Period Key is the end date of the month or quarter or year of the transaction date.
Load script:
Sub Generate_MetricsData(PeriodType, PeriodFuntion, EntityType, EntityCol)
TransactionData:
Load PeriodTypeas PeriodType,
PeriodFuntion(TransactionDate) as PeriodKey,
EntityType as EntityType ,
EntityCol as EntityName,
Sum(Orders) as Orders,
Sum(OrderAmount) as OrderAmount
Resident OrdersTbl
Group by PeriodType as PeriodType ,
PeriodFuntion(TransactionDate) ,
EntityType
EntityName
ServiceName,;
End Sub
Call Generate_MetricsData(Month, MonthStart(), Site, SiteName)
Is it possible to do it ? The reason I am trying to do this way is that I have more than 80 columns that I need to sum up this way. Otherwise I have to copy the same script for all period types, entity types.
Appreciate any help.
Hi GauTham,
if I understand you correctly, you need to load your data in the source table [Data Table:]. Next you need to download the Master Calendar (Generating Missing Data In QlikView), linking the previously downloaded data with him.
Then you will be able to solve their problems at the level of charts.
Regards,
Andrey
SourceTable:
Load * Inline
[
TransactionDate Country City Orders OrderAmount
24/01/2017 UK London 5 43037
25/01/2017 UK London 4 93644
24/01/2017 UK Manchester 6 45016
25/01/2017 UK Manchester 7 109985
24/01/2017 US NY 8 36616
25/01/2017 US NY 9 68354
24/01/2017 US NY 2 65487
25/01/2017 US LA 4 23171
24/01/2017 US LA 5 136289
](delimiter is \t);
Sub Generate_Table(SourceTableName, OutPutTableName, Date, Country, City, Qty, Amount)
Let SourceTableName = '$(SourceTableName)';
Let OutPutTableName = '$(OutPutTableName)';
Let Date = '$(Date)';
Let Country = '$(Country)';
Let City = '$(City)';
Let Qty = '$(Qty)';
Let Amount = '$(Amount)';
['$(OutPutTableName)']:
Load
'Month' as PeriodType,
MonthEnd($(Date)) as PeriodKey,
'City' as EntityType,
$(City) as EntityName,
SUM($(Qty)) as Orders,
SUM($(Amount)) as OrderAmount
Resident $(SourceTableName)
Group By MonthEnd($(Date)), $(City);
Concatenate
Load
'Month' as PeriodType,
MonthEnd($(Date)) as PeriodKey,
'Country' as EntityType,
$(Country) as EntityName,
SUM($(Qty)) as Orders,
SUM($(Amount)) as OrderAmount
Resident $(SourceTableName)
Group By MonthEnd($(Date)), $(Country);
Concatenate
Load
'Quarter' as PeriodType,
QuarterEnd($(Date)) as PeriodKey,
'City' as EntityType,
$(City) as EntityName,
SUM($(Qty)) as Orders,
SUM($(Amount)) as OrderAmount
Resident $(SourceTableName)
Group By QuarterEnd($(Date)), $(City);
Concatenate
Load
'Quarter' as PeriodType,
QuarterEnd($(Date)) as PeriodKey,
'Country' as EntityType,
$(Country) as EntityName,
SUM($(Qty)) as Orders,
SUM($(Amount)) as OrderAmount
Resident $(SourceTableName)
Group By QuarterEnd($(Date)), $(Country);
End Sub
Call Generate_Table ('SourceTable','TransactionData','TransactionDate','Country','City','Orders','OrderAmount');
Drop Table SourceTable;
What is the reason to create so many aggregated tables? Those aggregations are very easy within the UI and are usually very fast even with millions of records.
- Marcus