topic Re: Load script - Calling a subroutine & parsing variables in QlikView

Load script - Calling a subroutine & parsing variables

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Load script - Calling a subroutine & parsing variables

ahaahaaha — Fri, 24 Feb 2017 18:37:31 GMT

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

Re: Load script - Calling a subroutine & parsing variables

MK_QSL — Sat, 25 Feb 2017 00:00:44 GMT

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;

Re: Load script - Calling a subroutine & parsing variables

marcus_sommer — Sat, 25 Feb 2017 17:42:37 GMT

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