topic LOAD AND RESIDENT in QlikView

LOAD AND RESIDENT

Wed, 05 May 2010 16:18:52 GMT

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

Re. :LOAD AND RESIDENT

martin59 — Wed, 05 May 2010 16:21:34 GMT

Hello,

Replace this line :

sum(STATS_MVT.Movement date) as Numbers,

by this line :

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

Martin

LOAD AND RESIDENT

Wed, 05 May 2010 16:24:21 GMT

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

Re. :Re: LOAD AND RESIDENT

martin59 — Wed, 05 May 2010 16:27:05 GMT

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

Re. :Re: LOAD AND RESIDENT

Wed, 05 May 2010 16:36:29 GMT

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?

LOAD AND RESIDENT

Wed, 05 May 2010 16:41:05 GMT

And... "GROUP BY"??


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

LOAD AND RESIDENT

Wed, 05 May 2010 16:46:36 GMT

Are you sure about this reference?

sum(Expenses) as Expenses

could it be

sum(STATS_MVT.Expenses) as Expenses

LOAD AND RESIDENT

Wed, 05 May 2010 16:46:52 GMT

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?

LOAD AND RESIDENT

Wed, 05 May 2010 17:37:48 GMT

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

Re. :Re: LOAD AND RESIDENT

martin59 — Wed, 05 May 2010 19:57:33 GMT

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

Re. :Re: LOAD AND RESIDENT

Thu, 06 May 2010 08:58:20 GMT

Thank you for your response in french is very friendly.

For USER I don't need the where you are rigth but for this join I need a comparasion between two dates with one is between a interval of 3 seconds of the second date :


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'
 GROUP BY [STATS_MVT.User ID],ref_mvt_code;

INNER JOIN(temp1)
LOAD
 [User ID],
 [Partner code]
RESIDENT USERS;

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'
 GROUP BY [STATS_MVT.User ID],ref_mvt_code;

INNER JOIN(temp2)
LOAD
 [User ID],
 [Partner code]
RESIDENT USERS;

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 as Expenses
 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'
 GROUP BY [STATS_MVT.User ID],ref_mvt_code;

INNER JOIN(temp3)
LOAD
 [User ID],
 [Partner code]
RESIDENT USERS;

temp4:
LOAD
 [STATS_MVT.User ID] as [User ID],
 'Newdeposit' as ref_mvt_code,
 sum([STATS_MVT.Movement date]) as Numbers,
 sum(STATS_MVT.Amount) as Amount,
 0 as Expenses,
 [STATS_MVT.Movement date] as "Movement date",
 "Hour Movement date",
 "Minute Movement date",
 "Second Movement date"
 RESIDENT STATS_MVT WHERE ref_mvt_code ='Deposit'
 GROUP BY [STATS_MVT.User ID] ,[STATS_MVT.Movement date],
 "Hour Movement date",
 "Minute Movement date",
 "Second Movement date";

INNER JOIN(temp4)
LOAD
 [User ID],
 [Partner code],
 "First deposit date" as "Movement date",
 "HourFirst deposit" as "Hour Movement date",
 "Minute First deposit" as "Minute Movement date",
 "Second First deposit"
RESIDENT USERS
;

DROP TABLE STATS_MVT;

STATS_MVT_FIN:
LOAD
 [User ID],
 [Partner code],
 ref_mvt_code,
 Numbers,
 Amount,
 Expenses
RESIDENT temp4 where "Second Movement date" >= ("Second First deposit"-1) AND "Second Movement date" <= ("Second First deposit"+1)
;
drop table temp4;

CONCATENATE (STATS_MVT_FIN)
LOAD
 [User ID],
 [Partner code],
 ref_mvt_code,
 Numbers,
 Amount,
 Expenses
RESIDENT temp3
;
drop table temp3;

CONCATENATE (STATS_MVT_FIN)
LOAD
 [User ID],
 [Partner code],
 ref_mvt_code,
 Numbers,
 Amount,
 Expenses
RESIDENT temp2
;
drop table temp2;
CONCATENATE (STATS_MVT_FIN)
LOAD
 [User ID],
 [Partner code],
 ref_mvt_code,
 Numbers,
 Amount,
 Expenses
RESIDENT temp1
;

drop table temp1;
Have you an Idea for this please is very important for this indicator ?
I use CONCATENATE for UNION but I have an error :
Table non trouvée
CONCATENATE (STATS_MVT_FIN)
LOAD
 [User ID],
 [Partner code],
 ref_mvt_code,
 Numbers,
 Amount,
 Expenses
RESIDENT temp3

I don't know why , Do you have an idea please?</body>

Re. :Re: Re. :Re: LOAD AND RESIDENT

martin59 — Thu, 06 May 2010 19:01:57 GMT

Lorsque vous chargez deux tables qui ont le même nombre de champs et les mêmes noms de champs, QlikView se dit que c'est la même table divisée en deux. Il les concatene donc par défaut.

A cet endroit :


STATS_MVT_FIN:
LOAD
 [User ID],
 [Partner code],
 ref_mvt_code,
 Numbers,
 Amount,
 Expenses
RESIDENT temp4 where "Second Movement date" >= ("Second First deposit"-1) AND "Second Movement date" <= ("Second First deposit"+1)
;

La table créée a les mêmes champs que la table Temp2. Vous ne créez donc pas de table STATS_MVT_FIN, mais vous ajoutez vos données à la table Temp2.

Pour éviter cela, utilisez le mot clé NOCONCATENATE comme ceci :


STATS_MVT_FIN:
NOCONCATENATE LOAD
 [User ID],
 [Partner code],
 ref_mvt_code,
 Numbers,
 Amount,
 Expenses
RESIDENT temp4 where "Second Movement date" >= ("Second First deposit"-1) AND "Second Movement date" <= ("Second First deposit"+1)
;

En esperant que ça vous aide.

Martin

Re. :Re: Re. :Re: LOAD AND RESIDENT

Fri, 07 May 2010 08:15:09 GMT

Thank you every one for your help 🙂

I have a new problem with an other dashboard.

The topic is http://community.qlik.com/forums/p/28668/110314.aspx#110314

If you can help me ?

Thank you very much 🙂