Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Aran
Contributor
Contributor

Create Aggregates Through Load Script From REST API MYSQL Data In Sense Business (In the cloud)

This is a newbie question, so please forgive my ignorance.

I currently load data into Qlik Sense Business  from .csv files.  In my current load script, I load the data and then generate new tables with aggregate data I then use in some of my charts. This works fine.

I want to switch my connectors to load from a MYSQL DB through a REST API. I created a brand new app and APIs with which I am able to load the tables and make charts with the data. I am trying to generate the aggregates in an additional table I want to create through a load script, but I am making some mistake with the syntax and I can't figure it out. I'm loading the exact same fields and field values through both the API and the csv files, so the I think the issue is with the syntax I use in the script.

This works in the Qlik Sense Business  App which loads data from files, but not in the one which loads data from the DB through the REST API. The fields and field names are all the same as the csv files are generated using the same query as is used in the REST API

I've copied the scripts at the bottom of this post.

NB: I only included the "Section" in the load script related to the table I want to generate. The "Main" and the "Auto-generated section" sections both work fine in both apps. The top script which loads data and creates an aggregate table from a table generated from a .csv file currently works. The bottom script which is trying to do the same thing but using the REST API doesn't work.

This is the error I get:
"No qualified path for file: ***: LOAD [user_id], sum(if([type]='Deposit.',[deposit_added],0) ..."

I'm pretty sure the issue is in the part of the second script which I've colored red.

Thanks In Advance for any help.

Best

Aran

=====================

Loading From File - This Works...

==================

Customer_Aggregates:

load [User_ID],

// Total Cash Deposits of customers

// if(transaction_type='Deposit.',deposit_added,0) - if(note = 'Initial.',deposit_added,0)

// - if(note = 'Leaderboard.',deposit_added,0)

sum(if([transaction_type]='Deposit.',[deposit_added],0) - if(note = 'Initial.',deposit_added,0)

- if(note = 'Leaderboard.',deposit_added,0))

    as LifetimeCashDeposit,

    

// Total Rake = Deposits - Withdrawals for each customer

//  Need to deduct refunds from Deposits

    

    sum(if([transaction_type]='Deposit.',[deposit_added],0)-

    (if([transaction_type] = 'Withdrawal.',

if([winnings_deducted]>0,

[winnings_deducted],

[deposit_deducted]),

0))-if([transaction_type]='Deposit.',[winnings_added],0))

    as LifetimeRake

 

FROM [lib://CricSales:DataFiles/facts.csv]

     

(txt, codepage is 28591, embedded labels, delimiter is ',', msq)

 

group by [User_ID];

===================

Loading from MYSQL DB through REST API - This Doesn't Work...

===================

LIB CONNECT TO 'CricSales:Facts_II';

LOAD

[user_id],

 

// Total Cash Deposits of customers

// if(transaction_type='Deposit.',deposit_added,0) - if(note = 'Initial.',deposit_added,0)

// - if(note = 'Leaderboard.',deposit_added,0)

sum(if([type]='Deposit.',[deposit_added],0) - if(note = 'Initial.',deposit_added,0)

- if(note = 'Leaderboard.',[deposit_added],0))

    as LifetimeCashDeposit,

    

// Total Rake = Deposits - Withdrawals for each customer

//  Need to deduct refunds from Deposits

    

    sum(if([transaction_type]='Deposit.',[deposit_added],0)-

    (if([transaction_type] = 'Withdrawal.',

if([winnings_deducted]>0,

[winnings_deducted],

[deposit_deducted]),

0))-if([transaction_type]='Deposit.',[winnings_added],0))

    as LifetimeRake

 

FROM JSON "transactions" UDL

 

group by [user_id];

0 Replies