Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Preceding load issues when LOAD uses * to load multiple QVDs

Hello gurus,

I am attempting to perform a GROUP BY by using a preceding load on multiple QVD files (all with the same format) by using * in the filepath. It seems that only the first QVD found uses the preceding load the rest of the QVD files simply ignore this and creates their own data. I have also attempted to create the table before doing the load and forcing it to concatenate to it.

I have a possible workaround where I can create a function which return the list of files in the directory and I will perform a FOR EACH file loop do the load but I was wondering if there is something I am missing here. In the script execution you can see that the first QVD produced 60,000 rows after the GROUP BY function but then the second produced 6,200,000 rows. It is also creating a SYN table.

I have attached the code for your presual below. Any help will be appreciated.

SET vQvdFile_SPORTBOOK='$(vDirectory_QVD)\SportsBookAggrTest\KPI_FACT_SPORTSBOOK*.qvd';

UNQUALIFY '*';

FACT:

LOAD

  KPI_Date,

  %DATE_ID,

  %BUSINESSPARTNER_ID,

  SUM(GrossRevenue),

    SUM(GrossRevenuewithTheoreticalMargin),

    SUM(Cancelled),

    SUM(Rejected),

    SUM(Payback),

    SUM(BetCombiCount),

    SUM(PaidBack),

    SUM(NetRevenue),

    SUM(Winnings),

    SUM(OpenBets),

    SUM(Hold),

    SUM(BonusFreeBet),

    SUM(BonusRiskFreeBet),

    SUM(Bet_Counts),

    SUM(Bet_Slips),

    SUM(OpenBet_Counts),

    SUM(OpenBet_Slips),

    SUM(TheoreticalMargin)

GROUP BY

  KPI_Date,

  %DATE_ID,

  %BUSINESSPARTNER_ID;

LOAD KPI_Date,

     KPI_Date_ID AS %DATE_ID,

     Customer_ID AS %CUSTOMER_ID,

     BusinessPartner_ID AS %BUSINESSPARTNER_ID,

     Region, 

     Currency_Code,

     GrossRevenue,

     GrossRevenuewithTheoreticalMargin,

     Cancelled,

     Rejected,

     Payback,

     BetCombiCount,

     PaidBack,

     NetRevenue,

     Winnings,

     OpenBets,

     Hold,

     BonusFreeBet,

     BonusRiskFreeBet,

     Bet_Counts,

     Bet_Slips,

     OpenBet_Counts,

     OpenBet_Slips,

     TheoreticalMargin

FROM $(vQvdFile_SPORTBOOK)(qvd);

Table View.PNG

Script Execution.PNG

Regards,

Malcolm !

1 Solution

Accepted Solutions

Re: Preceding load issues when LOAD uses * to load multiple QVDs

Unfortunately that is the way it works, and yup it can be right pain until you get used to it.

You'll have to use your suggested work around of looping round all your required input files.

4 Replies

Re: Preceding load issues when LOAD uses * to load multiple QVDs

Unfortunately that is the way it works, and yup it can be right pain until you get used to it.

You'll have to use your suggested work around of looping round all your required input files.

Employee
Employee

Re: Preceding load issues when LOAD uses * to load multiple QVDs

You can´t do it this way, unfortunately

You should do :

FACT:

LOAD

     KPI_Date_ID AS %DATE_ID,

     Customer_ID AS %CUSTOMER_ID,

     BusinessPartner_ID AS %BUSINESSPARTNER_ID,

  SUM(GrossRevenue),

    SUM(GrossRevenuewithTheoreticalMargin),

    SUM(Cancelled),

    SUM(Rejected),

    SUM(Payback),

    SUM(BetCombiCount),

    SUM(PaidBack),

    SUM(NetRevenue),

    SUM(Winnings),

    SUM(OpenBets),

    SUM(Hold),

    SUM(BonusFreeBet),

    SUM(BonusRiskFreeBet),

    SUM(Bet_Counts),

    SUM(Bet_Slips),

    SUM(OpenBet_Counts),

    SUM(OpenBet_Slips),

    SUM(TheoreticalMargin)

FROM $(vQvdFile_SPORTBOOK)(qvd)

GROUP BY

  KPI_Date_ID,

  Customer_ID,

  BusinessPartner_ID;

Not applicable

Re: Preceding load issues when LOAD uses * to load multiple QVDs

Thanks was afraid of that

Re: Preceding load issues when LOAD uses * to load multiple QVDs

Do have look at the suggestion by cleveranjosthough.  He often comes up with cunning solutions.

Community Browser