topic Re: For each...next. Customer start date and end date from acitivty history table. in App Development

For each...next. Customer start date and end date from acitivty history table.

alextomlins — Mon, 10 Sep 2018 10:07:44 GMT

Hi Guys,

I am not familiar with loops but I believe i need a foreach...next loop.

I am trying to take a table with customer activities such as subscribe, unsubscribe. From this i would like a start date and an end date BUT the problem is that i can't just use min and max because the customers may have multiple subscriptions. So I need to go through each user and use some logic to determine their start and their end date and create a row for this one subscription and then start a loop again for that customer and create another row for a second subscription if there is one.

If you are unable to help with the above -I am new to loops etc in Qlik and if anyone has any documentation explaining the syntax and uses of all the different type that would be great.

Even if you don't know the exact answer i am looking for the rough approach i would need to take.

Thanks again Qlik community.

Alex

Re: For each...next. Customer start date and end date from acitivty history table.

vikasmahajan — Mon, 10 Sep 2018 10:47:57 GMT

Go through this link

Load Multiple excel sheets using For loop

Qlikview Load Multiple Excel Sheets (Concatenate & Join tables)

https://community.qlik.com/thread/252808

Vikas

Re: For each...next. Customer start date and end date from acitivty history table.

PunamWagh — Mon, 10 Sep 2018 10:49:45 GMT

Not sure wat u r looking for bt u can try following-

Data:

load * inline [

type , customer , Date

subscribe , Abc , 8/10/2018

subscribe , Abc , 7/10/2018

subscribe , Abc , 6/10/2018

subscribe , Abc , 5/10/2018

];

final:

load Date(min(Date)) as Start_Date,

date(Max(Date)) as End_Date,

customer

Resident Data

Group by customer;

drop Table Data;

exit script;

it will be great if u can provide sample data with expected output

Re: For each...next. Customer start date and end date from acitivty history table.

alextomlins — Mon, 10 Sep 2018 11:50:13 GMT

Hi Punam,

i've done this as a resident load but the problem is that customers may have resubscribed later and then unsubscribed again under the same Customer ID. At the moment i am trying to do a for each...next loop to go through each customer id and create a row for each customer that details either first subscription or second subscription etc. I have no idea how to do it though

for now i am starting with just trying to use this 'for each next loop' to get the start date for each customers. at the moment i can only get the CustomerID in the loop. This is what i am using - how do i get another field value into it from the same table so that i can later apply some logic to them?

For each a in FieldValueList('CustomerID')

Data:

LOAD

'$(a)' & '-' & RecNo() as CustomerID

Autogenerate 1;

next a

Thanks,

Alex

Re: For each...next. Customer start date and end date from acitivty history table.

alextomlins — Mon, 10 Sep 2018 11:54:37 GMT

Hi these are slightly helpful but i was hoping to have more about evaluating field values using a for each loop?

I would actually like to find a way of bringing in multiple fields from other tables intto this bit of script;

For each a in FieldValueList('CustomerID')

Data:

LOAD

'$(a)' & '-' & RecNo() as CustomerID

Autogenerate 1;

next a