Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a problem when I want do this script :
temp1:
LOAD
[STATS_MVT.User ID] as [User ID],
ref_mvt_code,
sum(STATS_MVT.Movement date) as Numbers,
sum(STATS_MVT.Amount) as Amount,
sum(Expenses) as Expenses
RESIDENT STATS_MVT WHERE ref_mvt_code ={'Deposit','Withdrawal'};
INNER JOIN(temp1)
LOAD
[User ID],
[Partner code]
RESIDENT USERS where temp1.[User ID] = [User ID];
Qlikview said that:
Error in expression:
')' expected
temp1:
LOAD
[STATS_MVT.User ID] as [User ID],
ref_mvt_code,
sum(STATS_MVT.Movement date) as Numbers,
sum(STATS_MVT.Amount) as Amount,
sum(Expenses) as Expenses
RESIDENT STATS_MVT WHERE ref_mvt_code ={'Deposit','Withdrawal'}
I don't understand why, please can you help???
Hello,
Replace this line :
sum(STATS_MVT.Movement date) as Numbers,
by this line :
sum([STATS_MVT.Movement date]) as Numbers,
Martin
Hello,
Replace this line :
sum(STATS_MVT.Movement date) as Numbers,
by this line :
sum([STATS_MVT.Movement date]) as Numbers,
Martin
This looks like a wrong expression to me
sum(STATS_MVT.Movement date) as Numbers,
I have also some doubts about your WHERE condition but could be wrong
Yes, I have'nt take care of the WHERE syntax.
You must to enter :
WHERE ref_mvt_code ='Deposit' OR ref_mvt_code='Withdrawal'
and not
WHERE ref_mvt_code ={'Deposit','Withdrawal'}
Thank you all for your help 🙂
have an error in Expenses :
Expenses,
temp1:
LOAD
[STATS_MVT.User ID] as [User ID],
ref_mvt_code,
sum([STATS_MVT.Movement date]) as Numbers,
sum(STATS_MVT.Amount) as Amount,
sum(Expenses) as Expenses
RESIDENT STATS_MVT WHERE ref_mvt_code ='Deposit' OR ref_mvt_code='Withdrawal'
temp1:
LOAD
[STATS_MVT.User ID] as [User ID],
ref_mvt_code,
sum([STATS_MVT.Movement date]) as Numbers,
sum(STATS_MVT.Amount) as Amount,
sum(Expenses) as Expenses
RESIDENT STATS_MVT WHERE ref_mvt_code ='Deposit' OR ref_mvt_code='Withdrawal';
INNER JOIN(temp1)
LOAD
[User ID],
[Partner code]
RESIDENT USERS where temp1.[User ID] = [User ID];
temp2:
LOAD
[STATS_MVT.User ID] as [User ID],
ref_mvt_code,
sum([STATS_MVT.Movement date]) as Numbers,
sum(STATS_MVT.Amount) as Amount,
sum(Expenses) as Expenses
RESIDENT STATS_MVT WHERE ref_mvt_code ='Chargeback';
INNER JOIN(temp2)
LOAD
[User ID],
[Partner code]
RESIDENT USERS where temp2.[User ID] = [User ID];
temp3:
LOAD
[STATS_MVT.User ID] as [User ID],
ref_mvt_code,
sum([STATS_MVT.Movement date]) as Numbers,
sum(STATS_MVT.Amount) as Amount,
0
RESIDENT STATS_MVT WHERE ref_mvt_code ='Bonus' or ref_mvt_code ='Offer'
or ref_mvt_code ='Filleul' or ref_mvt_code = 'Parrain' or ref_mvt_code ='Cashback' or ref_mvt_code = 'Freebet'
or ref_mvt_code ='Bet' or ref_mvt_code ='Livebet' or ref_mvt_code ='Cancel'
or ref_mvt_code ='Win'
or ref_mvt_code ='Backbet'
or ref_mvt_code ='Backlivbet'
;
INNER JOIN(temp3)
LOAD
[User ID],
[Partner code]
RESIDENT USERS WHERE temp3.[User ID] = [User ID];
temp4:
LOAD
[STATS_MVT.User ID] as [User ID],
'Newdeposit',
sum([STATS_MVT.Movement date]) as Numbers,
sum(STATS_MVT.Amount) as Amount,
0,
"Day Movement date",
"Month Movement date",
"Year Movement date" ,
"Hour Movement date",
"Minute Movement date",
"Second Movement date"
RESIDENT STATS_MVT WHERE ref_mvt_code ='Deposit';
INNER JOIN(temp4)
LOAD
[User ID],
[Partner code]
RESIDENT USERS where temp4.[User ID] = [User ID]
AND temp4."Day Movement date" = USERS."Day First deposit"
AND temp4."Month First deposit" = USERS."Month Movement date"
AND temp4."Year First deposit" = USERS."Year Movement date"
AND temp4."Hour First deposit" = USERS."Hour Movement date"
AND temp4."Minute First deposit" = USERS."Minute Movement date"
AND USERS."Second Movement date" <= ("Second First deposit"-1) and USERS."Second Movement date" >= ("Second First deposit"+1)
;
You now why?
And... "GROUP BY"??
LOAD
a,
b,
c,
sum(d) as d
RESIDENT xxxxxxxx WHERE zzzzz
GROUP BY a,b,c;
Are you sure about this reference?
sum(Expenses) as Expenses
could it be
sum(STATS_MVT.Expenses) as Expenses
Thanks for the group by 🙂
I have now this error
Table non trouvée
INNER JOIN(temp1)
LOAD
[User ID],
[Partner code]
RESIDENT USERS where temp1.[User ID] = [User ID]
EDIT: Sorry it's a error name of Users 🙂
Champ non trouvé - <temp1.[User>
INNER JOIN(temp1)
LOAD
[User ID],
[Partner code]
RESIDENT USERS where temp1.[User ID] = [User ID]
It's a problem with where in The join but what problem?
How do the where clause for the Inner join please? you have an idea?
Vu que tes erreurs sont en français, je vais te répondre en français !
D'une part, pour tes requêtes avec WHERE tu dois utiliser les noms de champs que tu as dans ta table source. Ici, on dirait que ton champ initial s'appelle [User ID] et non temp1.[User ID].
Ensuite, si tu veux faire une jointure, tu dois utiliser la syntaxe suivante :
Tab1:
[NOCONCATENATE] LOAD *
RESIDENT TmpTable;
[OUTER] JOIN (Tab1)
LOAD *
RESIDENT Tab2;
Tu n'as pas de clause WHERE à insérer comme dans du SQL, la syntaxe AQL est quelque peu différente. Jette un coup d'oeil à l'application jointe, elle pourra peut-être t'aider.
Martin