For Loop: Create a flag

[CorvoAttano]

Hello Community,

I have an issue I cannot resolve, even though it should be pretty simple.

Im trying to create a for .. next loop, which should fulfill the following requirements:
Each customer no. (CUSTOMER_NO) is visited once every 2 weeks by a sales person (SALER). I defined a Flag (FLAG_SELL), which says 1 if he was there and 0 if he wasn´t. Additionally I have to check, if the last visit is older than 2 weeks.

So the fields are:
- CUSTOMER_NO

- FLAG_SELL

- SALER

The flag has to say "Visited correctly" or "Did not visit".

I have no idea how to do that.

Could anyone of you help me?

Thank you in advance!

[tm_burgers]

Rather than a Loop.. 

Create a new table grouped by Customer_No. Like below?

// New Table - adjust with your correct fields

LastVisit:
LOAD 
  CustomerNo,
  max(VisitDate) as LastVisit,
  if(max(VisitDate)>date(today()-14),1,0) as FLAG_SELL
Resident YourTable
Group by Customer_No

[CorvoAttano]

Hello,
no that does not solve my problem. Visits occured over the last two years and it has to be checked individually between them if they occured in a 2 week cycle. So max the table will only check for the last two weeks.

Still thank you!

[MayilVahanan]

HI 

May be try like below

Load *, If(NoOfDays <= 14, 1, 0) as FLAG_SELL;

Load Customer_No, Saler, VisitDate, Peek(VisitDate) as LastVisitDate, If(Peek(Customer_No) = Customer_No and Saler = Peek(Saler), Peek(VisitDate) - VisitDate) as NoOfDays

resident tablename 

order by Customer_No,Saler;

[CorvoAttano]

Hello MayilVahanan,

Thank you very much for your time, but flag sells is already defined: Customer did buy something.

Plus I am adviced to do a loop. I am unable to solve this without a loop, but I still have to do one.

Flag_Sell is already 1 (Sell), 0 (No Sell).

The number of days have to be calculated for each customer number.

Any ideas for that? I would be very grateful!!

[CorvoAttano]

Sorry correction: I am able to solve this without a loop, but I still have to use a loop.

[MayilVahanan]

Hi

Can you provide sample data and expected output?

I think, no need to use loop.. but after see ur data and expected output only need to decide it. thanks

[CorvoAttano]

PlannedVisits:
load * Inline [Customer_No,VisitDay];

let Customer_No = peek('Customer_No',$(i),'Customer');
let Frequency = peek('Frequency',$(i),'Customer');
let VisitDay = peek('VisitDay',$(i),'Customer');
let StartDate = num('01.01.2020');

// for i=1 to NoOfRows(Customer) step 1
if $(Frequency)<> 6 then
let Counter= floor((num(yearend(AddYears(today(),1))) -$(FirstVisit))/(7* $(Frequency)));

for o= $(FirstVisit) to num(yearend(AddYears(today(),1))) step (7*$(Frequency))
PlannedVisits:
concatenate load
$(Customer_No) as %Customer_No,
date($(o),'DD.MM.YYYY') as VisitDay
autogenerate 1;
next o;
else ;

next i;

Im getting an error on "next i": The control statement is not correctly matched with its corresponding start statement.

I checked google, but the only solution is to parse the rest of the code. I do not have any code after "next i".

Hope this helps.

[CorvoAttano]

Expected output:

For each customer number should there be a date in a 2 week cycle.

[MayilVahanan]

HI 

Based on ur code, there is no starting statement for "i".