loop causing duplicate records which i want to erase

[random_user_3869]

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

[edwin]

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()

[random_user_3869]

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 !

[edwin]

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

[random_user_3869]

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

[edwin]

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

[vinieme12]

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-oth...

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