topic Re: loop causing duplicate records which i want to erase in App Development

loop causing duplicate records which i want to erase

random_user_3869 — Wed, 11 May 2022 16:05:33 GMT

hello everyone,

i have the following script

FOR i = 1 TO 4

LET vL.BeginingDate = MonthStart(Addmonths(today(),-(6+'$(i)')));
LET vL.EndDate = MonthEnd(Addmonths(today(),-'$(i)'));

Trace DATE DEBUT ###### $(vL.BeginingDate) #########;
Trace DATE FIN ###### $(vL.EndDate) #########;

TEMP:
Load
[CUSTOMER ID],
[Amount],
[Min Date Customer],
'$(i)' AS [FLAG NEW CUSTOMER],
[ORDER ID]

WHERE [Date Customer] >= '$(vL.DatePeriodDebut)' AND [Date Customer] <= '$(vL.DatePeriodFin)';

//Attempt to reduce the duplicate records because of the flag field "[FLAG NEW CUSTOMER]".
So i only want to get the firs record of this perticular field.
Because i goes from 1 to 4 i only want to get the record where a customer has ordered a particular item.

basically for the FLAG NEW CUSTOMER] it will get n values from 1 to n if the order id is in the date period
So a same order id can be there for each loop which i don't want

to get the firs value of [FLAG NEW CUSTOMER] field i tried

Option 1
Load Distinct
min([CUSTOMER ID]),
min([Amount]),
min([FLAG NEW CUSTOMER]),
[RECEIPT ID],
[Date Customer]

Resident TEMP
Group By
[RECEIPT ID],
[Date Customer];

I got for the field FLAG NEW CUSTOMER] all values at 1 which is impossible.
I used minstring it didn't work.

anyone can help ?

thanks

Re: loop causing duplicate records which i want to erase

edwin — Wed, 11 May 2022 18:19:08 GMT

it sounds like you wanted to the the 6-month window where the customer ordered a product the first time. is that the requirement? maybe there is a way to go about this without using loops and just using table operations. if you have a huge data set the loop might be slow.

if you can provide sample data and the expected result it would be helpful.

if you still want to use your code, i would suggest changing this part : '$(i)' AS [FLAG NEW CUSTOMER],

to $(i) AS [FLAG NEW CUSTOMER],

it then becomes numeric and you should be able to use min()

Re: loop causing duplicate records which i want to erase

random_user_3869 — Wed, 11 May 2022 18:39:36 GMT

I use a loop because afterward i need to calculate the cumulative period from previous month n to other periode as well.

The flag field allow me to:

if it equal 1 then i now that it's the rolling 6 month started from previous month

it it equal 2 it is previous month minus 2 and so on

thank you for your input.

i will try and let you know !

Re: loop causing duplicate records which i want to erase

edwin — Wed, 11 May 2022 18:48:57 GMT

sure try but just suggesting that table operations are a lot faster than loops

Re: loop causing duplicate records which i want to erase

random_user_3869 — Wed, 11 May 2022 19:18:57 GMT

here are the mltiple records for a single receipt id.

Here i only want the first record.

Even with your advice when i apply min i get one for every single value which is impossible

Re: loop causing duplicate records which i want to erase

edwin — Wed, 11 May 2022 19:50:20 GMT

i think your problem is here:

min([CUSTOMER ID]),
min([Amount]),
min([FLAG NEW CUSTOMER]),
[RECEIPT ID],
[Date Customer]

Resident TEMP
Group By
[RECEIPT ID],
[Date Customer]

you are aggregating across all customers which will not yield any useful information. if you feel you really need to use the script you have, pls post it and add sample data and outcome so others can see what is causing the problem otherwise everyone will just be guessing

Re: loop causing duplicate records which i want to erase

vinieme12 — Wed, 11 May 2022 20:34:23 GMT

FOR i = 1 TO 4

LET vL.BeginingDate = MonthStart(Addmonths(today(),-(6+'$(i)')));
LET vL.EndDate = MonthEnd(Addmonths(today(),-'$(i)'));

You are loading data for overlapping periods here!

If today is 12May2022

Loop 1

Beginning date = 1dec2021

End date= 30apr2022

Loop2

Beginning date = 1jan2021

End date= 31march2022

You don't need to duplicate your data for calculating rolling months

Refer this post

https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-other/ta-p/1483033

There is plenty of more content on rolling sums, but you need not duplicate the data for this