Load script - Calling a subroutine & parsing variables

[Anonymous]

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.

[ahaahaaha]

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

[MK_QSL]

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;

[marcus_sommer]

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