topic Re: Foor Loop in QlikView

Foor Loop

jood_ahmad — Wed, 25 Nov 2020 16:16:04 GMT

Dears,

I'm Trying to use for loop but it doesn't work correctly; the idea is:

* we have sub-points: for each customer, only 1 point per day regardless how many time he called.

* we have main points: once the customer called 6 times in different days he will gain 1 Main point or 1 gift.

I need to record pints for each Phone number when he reaches the 6 calls to give him 1 point and if he has more than that let us say X customer he calls 8 times so he will have 1 point and 2 sub-points.

And Z customer has 12 calls so he has 2 points.

I use the below load:

Load

count(distinct PHONE&'|'&Date) as Key_,

PHONE

from A;

let vLoop= Peek('Key_',0,'A');

let vPhoneNum= Peek('PHONE',0,'A');

Drop Table A;

FOR i= 0 to $(vLoop) -1

FOR x= $(i) to 6

let P = (0 + $(x)) ;

Free:

LOAD

$(vPhoneNum) as Key_Phone,

$(P) as #Free

AutoGenerate($(vPhoneNum))

;

NEXT ;

then i will left join the Free table to table by using the Key_Phone.

any Idea please;

stevedark‌

Message was edited by: Ahmad Kastero sorry steve i wrote it manually; so what i try to do is i have customers with X call number per day and the application will only count one point for each customer per day. if the customer reaches 6 calls he will have a 1 gift otherwise i will show the points in another variable. let us say the customer called 8 times; so he will have 1 gift and 2 points.

Re: Foor Loop

stevedark — Mon, 12 Mar 2018 16:17:08 GMT

Hi Ahmad,

Not quite sure what you are trying to achieve, but I can spot a couple of syntax errors in your code.

You are getting the count of calls into a field Key_, but when you peek out the value you are referring to Key. The missing underscore will break things.

Similarly, the phone number you are using both Phone and PHONE, field names are always case sensitive, so watch out for this.

You have the parameter for the AutoGenerate function as vPhoneNum. This function requires an integer number, which defines how many rows of data will be generated. I suspect that the parameter you want for the AutoGenerate is always 1, rather than a variable.

Hope that helps.

Steve

Re: Foor Loop

jood_ahmad — Tue, 13 Mar 2018 05:27:19 GMT

sorry steve i wrote it manually; so what i try to do is i have customers with X call number per day and the application will only count one point for each customer per day. if the customer reaches 6 calls he will have a 1 gift otherwise i will show the points in another variable. let us say the customer called 8 times; so he will have 1 gift and 2 points.

Re: Foor Loop

jood_ahmad — Tue, 13 Mar 2018 05:43:26 GMT

What i did is:

1. i create a key between the Phone number and the Date and get distinct.

2. peek the key and the phone.

3. 1loop from 0 to the # of records.

4. 2loop to loop the phone number 6 time and give me the # of the gift.

actually, i'm not that perfect in Foor Loop. so is that logic right?

Re: Foor Loop

stevedark — Tue, 13 Mar 2018 09:11:43 GMT

Hi Ahmad,

It sounds to me like you are over complicating things a bit.

To my mind it needs to be something like;

Calls:

LOAD

1 as CallCount,

Phone & ':' & Date(CallTime, 'YYMMDD') as PhoneKey,

CallTime,

Destination,

Duration,

etc....

FROM [yourdatasource]

;

Points:

LOAD

PhoneKey,

DailyCalls,

floor(DailyCalls/6) as Gifts,

(DailyCalls - (floor(DailyCalls/6) * 6)) as Points

;

LOAD

PhoneKey,

sum(CallCount) as DailyCalls

RESIDENT Calls

GROUP BY PhoneKey

;

That will work things out on a daily basis (I think). Not sure if subscribers can roll over points from day to day to accrue gifts? If so, you would need to amend the Key to only be phone number, or phone number / month etc..

Hope that makes sense?

Steve

Re: Foor Loop

jood_ahmad — Tue, 13 Mar 2018 15:28:46 GMT

Thank Steve, Actually, i did that on the front end but what actually trying to get by using the For Loop is (and this is what i actually forget to mention) is once the customer reaches the 6 point i will take the max date so I can rely on that date to calculate the expiry date the gifts and control it.

I don't want it to become complicated but maybe i didn't explain it in the right way.

Thank you very much.

Re: Foor Loop

stevedark — Wed, 14 Mar 2018 10:57:11 GMT

Hi Ahmad,

In that case you probably want to be doing a load with a peek to read previous row values.

In order to do this you need to load into a temporary table, and then load from that table with a RESIDENT load and load in a specific sort order (PhoneNumber, Date) and then you can increment a value, with code like this:

if(Peek(PhoneNumber, -1) = PhoneNumber, Peek(AccumulatedCalls, -1) + 1, 0) as AccumulatedCalls,

You can then do a preceding load on this to do work over the AccumulatedCalls.

You can then also take the date of the call when AccumulatedCalls reaches 6 and have that as the gift date.

I still haven't got my head around your data or requirement, so I can't say for sure what you need, but those kind of techniques may help.

Steve