LOAD AND RESIDENT

[#1]

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???

[martin59 · Accepted solution]

Hello,

Replace this line :

sum(STATS_MVT.Movement date) as Numbers,

by this line :

sum([STATS_MVT.Movement date]) as Numbers,

Martin

[#3]

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

[martin59]

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'}

[#5]

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?

[#6]

And... "GROUP BY"??

LOAD
 a,
 b,
 c,
sum(d) as d
RESIDENT xxxxxxxx WHERE zzzzz
GROUP BY a,b,c;

[#7]

Are you sure about this reference?

sum(Expenses) as Expenses

could it be

sum(STATS_MVT.Expenses) as Expenses

[#8]

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?

[#9]

How do the where clause for the Inner join please? you have an idea?

[martin59]

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