Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
david_ze
New Contributor III

Help in script

Dear All, I'll appreciate your help in how to make a clculations within the sript.

I would like to calculate our customer-base with the following logic:

The data looks like this

IDConnect DateDis-Connect Date
101/01/200401/01/2004
202/01/200405/01/2004
302/01/200403/01/2004
402/01/2004
502/01/200404/01/2004
605/01/2004
705/01/200407/01/2004
805/01/200410/01/2004
905/01/2004
1005/01/2004
1109/01/200412/01/2004
1209/01/2004

At each date I would like to have a number which is a calculation of the existing connected customers.

Means that, on the date of 2/1/2004 i will have 4 customers (5 connected till 2/1/04 and one dis-connected).

On the Feb/5 I will have 6 customers (10 connected and 4 dis-connected).

I'll appreciate your help on how to handle this in the script.

Thanks in advance

David

2 Replies
luciancotea
Valued Contributor

Re: Help in script

First step is to transform your data to this:

IDDateActionCounter
11/1/2004Connect1
11/1/2004Disconnect-1
22/1/2004Connect1
32/1/2004Connect1
42/1/2004Connect1
52/1/2004Connect1
33/1/2004Disconnect-1
54/1/2004Disconnect-1
65/1/2004Connect1
75/1/2004Connect1
85/1/2004Connect1
95/1/2004Connect1
105/1/2004Connect1
25/1/2004Disconnect-1
77/1/2004Disconnect-1
119/1/2004Connect1
129/1/2004Connect1
810/1/2004Disconnect-1
1112/1/2004Disconnect-1

Then, it's a simply "Counter" sum operation.

joonasjaspi
New Contributor III

Re: Help in script

I would create separate Flag table with this:

LOAD

ID,

date([Connect Date]+Iterno()-1) as Date

if(iterno()=1, 1,0) as Flag_Start,

if[Connect Date]+iterno()-1= [Dis-Connect Date],0,1) as Flag_Connected

if[Connect Date]+iterno()-1= [Dis-Connect Date]1,0) as Flag_Disconnected

Resident Data

while [Connect Date]+iterno()-1) <= IF(isnull([Dis-Connect Date]),today(),[Dis-Connect Date]);

With this you can easily get number of connected ID with Sum(Distinct {<Flag_Connected={1}>} ID), but you can easily answer question like how many customers connected/disconnected this day/this month/this year by summing start and end flags.

Community Browser