Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

How to make a custom counter into if function when scripting

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

12 Replies
OmarBenSalem
Esteemed Contributor

Re: How to make a custom counter into if function when scripting

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_in
Valued Contributor

Re: How to make a custom counter into if function when scripting

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

Not applicable

Re: How to make a custom counter into if function when scripting

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

Not applicable

Re: How to make a custom counter into if function when scripting

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
Esteemed Contributor

Re: How to make a custom counter into if function when scripting

Can you share your qvf file?

Not applicable

Re: How to make a custom counter into if function when scripting

Hi This is the qvf file .

I just started the developing it .

Thank you

OmarBenSalem
Esteemed Contributor

Re: How to make a custom counter into if function when scripting

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

Capture.PNG

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.

Not applicable

Re: How to make a custom counter into if function when scripting

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

Not applicable

Re: How to make a custom counter into if function when scripting

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