I am new to loops in Qlik. I have a rough idea from Python but generally new to it. I need to look through a customer ID to apply some logic to extract datetimes from another table.
This table is a table of differing event types, so subscribes, unsubscribes and other events. The other big thing is that customers have multiple subscriptions. I want to start my process by building a loop to just go through the customer IDs in another table and find the min date where the eventtype is 'Active'. I do not want to use a resident load because i will be applying some further logic on other engagement types but i do not know how to expand on my For each next.
What i have at the moment is
For each a in FieldValueList('CustomerID')
Data:
LOAD
'$(a)' as CustomerID,
//Inside here i need to then lookup the customer ID against the event type table and find the min date where the event type is active.
//after this I will need apply further If logic in combination with other events type scenarios.
For me it sounds like you would achieve your solutions much better and more efficiently by doing a LOAD with GROUP BY and aggregations like this for the first scenario you mention.
LOAD
CustomerID,
Min(Date) AS MinDate
RESIDENT
EventTypeTable
WHERE
EventType = 'Active'
GROUP BY
CustomerID;
You might be able to extend this single LOAD above with the additional scenarios or you could do similar LOADs repeatedly to solve them.
For/Next and lookups are extremly slow compared with what I propose.
I don't really believe that RESIDENT LOAD will stop you from doing further logic.
I think looping over the customers may perform quite slowly. and you will still need to do resident loads to get, for example, the minimum active type date field. And you may have to perform several loads for the 'further logic' you need.
>>I do not want to use a resident load because i will be applying some further logic on other engagement types but i do not know how to expand on my For each next.
Not sure what the problem is here. How does the resident load use the For Each statement?
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Sorry maybe i do not understand my own problem. I started with a resident load and min and max start date but there are several type of events and one customer can have multiple subscriptions so a min and a max could span two subscriptions. So I was hoping to loop through each customer and create a row for the first active they have and the first inactive, a second inactive would create another row for a second subscription. There are also customers who will not have subscribed and i will need to getdate() type function.
It's mostly the min and max spanning two subscriptions that is the problem.
Sorry if i'm struggling here - i just struggled with resident because i was using nested if statements and couldn't evaluate on the fly.
It might not be possible to achieve the calculations with a single LOAD ... RESIDENT statement but multiple should work out since you need different GROUP BY field combinations.
