How to make a custom counter into if function when scripting

[#1]

Hello all,

I'm new in Qliksense and  I need to make two counters like here .

counter = 0

counter1= 1

if ([recurring.start_date] > [appointments.start_date] or [recurring.end_date]< [appointments.end_date])   and [recurring.kind]='open' then

counter = counter+1;

else

counter1= counter1 +1;

end if ;

there is two tables with  columns start and end dates .  ( recurring and appointment) one counter for the appointments withing recurrent dates and one counter for the appointments outside the recurrent  .

i don't know if it works because i didn't find  the counters in the other views .

i don't even know if this might be done in scripting part.

Thanks

[OmarBenSalem]

How were you doing this?

I mean, you can do as you were doing, then load the whole thing.

You don't even need to understand the way Qlik does the scripting

Please refer to this thread :

https://community.qlik.com/thread/254946?sr=inbox&ru=242041

[hector_munoz]

Hi Moez,

You can do the following:

TABLE_2:

LOAD              Sum(if ([recurring.start_date] > [appointments.start_date] or

                          [recurring.end_date]< [appointments.end_date])

                          and [recurring.kind]='open', 1, 0)) AS counter,

Sum(if ([recurring.start_date] > [appointments.start_date] or

                          [recurring.end_date]< [appointments.end_date])

                          and [recurring.kind]='open', 0, 1) AS counter1

RESIDENT    TABLE_1;

LET vsCounter = Peek('counter', 0, 'TABLE_2');

LET vsCounter1 = Peek('counter1', 0, 'TABLE_2');

Regards,

H

[#4]

thanks for your answer

I am sorry I am new in qliksense

I opened a new section :

i puted this :

LIB CONNECT TO 'PostgreSQL_localhost';

recurring_events:

LOAD              Sum(if ([recurring.start_date] > [appointments.start_date] or

                          [recurring.end_date]< [appointments.end_date]

                          and [recurring.kind]='open', 1, 0)) AS counter,

                    Sum(if ([recurring.start_date] > [appointments.start_date] or

                         [recurring.end_date]< [appointments.end_date]

                         and [recurring.kind]='open', 0, 1)) AS counter1

RESIDENT appointments;

LET vsCounter = Peek('counter', 0, 'recurring_events');

LET vsCounter1 = Peek('counter1', 0, 'recurring_events');

Qliksense doesn't recognize tables appointments and recurring_events, I tried with [] too.

I also tried to parse it and to put it before the SELECT in the other SECTION .. nothing

[#5]

hello thanks for the answer .

I didn't do anything before , I just started . thanks for the link I have two tables: appointments and recurring events. I want to make a counter to know the number of appointments outside the dates of the recurring events. As well as a counter for the appointments in the recurring events (start_date, end_date).

The point of all of this, is to create a pie chart containing the two counters.

[OmarBenSalem]

Can you share your qvf file?

[#7]

Hi This is the qvf file .

I just started the developing it .

Thank you

[OmarBenSalem]

Before anaything, drag Section and put it before Section 2 than reload:

In fact, Qlik will execute the script section by section; from up down.

This is why Qlik doesn't recognize your table appointement, because you called it before defining it.

[#9]

Thanks it's very useful i didn't know it .
now qlik recognize the tables but not the variables " recurring.start_date" . . .

thanks for your answers

[#10]

can i declare two tables like :

appointments:

recurring_events:

LOAD              Sum(if ([recurring.start_date] > [appointments.start_date] or

                          [recurring.end_date]< [appointments.end_date]

                          and [recurring.kind]='open', 1, 0)) AS counter,

because qlik doesn't recognize appointments.start_date from the table appointments ?

thanks