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];
