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