12 Replies Latest reply: Mar 16, 2018 6:27 AM by Axel Beaugrand RSS

    if match : Confirm function :

    Axel Beaugrand

      Hi,

       

      Can someone please tell me if it's possible to use Match function in my request?

      In my application I've two differents date: entry and exit

       

      I created a special calendar in order to know the numbers of présents, I find the solution by intervalmatch..

       

      Now, I would like to calculate the numbers of peole entry and exit :

      I've no idea to translate this condition in my table...

       

      I tried the condition but is not working.

      Maybe I need to use the if match but any one can help me.

      if(Entry_date = Present_date,1,0) as Flag_Enty

       

      Thanks!

       

      A.

        • Re: if match : Confirm function :
          youssef belloum

          Hi,

           

          if(Entry_date = Present_date,1,0) as Flag_Enty


          the syntax looks good

           

          can you take a screen shot from your script (where you have this Flag field) and another one from your table sctructure ?

          • Re: if match : Confirm function :
            Gerold Roser

            benutz du eine chartbox zum anzeigen ?

            • Re: if match : Confirm function :
              Bill Markham

              Maybe :

               

              if ( Match ( Entry_date , concat ( Present_date , ',' ) ,1,0) as Flag_Enty

               

               

              Have not tested it so may have a typo and may need a dollar expansion around the concat like this :

               

              if ( Match ( Entry_date , $(concat ( Present_date , ',' ))  ,1,0) as Flag_Enty

              • Re: if match : Confirm function :
                Axel Beaugrand

                Please  youssef belloum           

                My real script :

                PRESENCE_REELLES:
                //load*,
                //if ( Match ( DATE_ENTREE , concat( Date_Pres , ',' )) ,1,0) as Flag_Enty; NOT WORKING

                load
                @cle_dossier,
                @cle_patient as @DOSSIER_PATIENT,
                DOSSIER_DATE_ENTREE as DATE_ENTREE,
                DOSSIER_DATE_SORTIE as DATE_SORTIE,
                DATE_NUM_ENTREE as NUM_ENTREE
                Resident DOSSIERS;

                inner join intervalmatch (Date_Pres)
                load distinct DATE_ENTREE,DATE_SORTIE
                Resident PRESENCE_REELLES;

                left join (PRESENCE_REELLES)
                Calendrier_Pres:
                load*
                resident Calendrier_Pres_1;
                drop table Calendrier_Pres_1;

                  • Re: if match : Confirm function :
                    youssef belloum

                    the flag looks good

                     

                    try this:

                     

                    LET vMinDate = Num(Makedate(2017,1,1));

                    LET vMaxDate = Num(Makedate(Year(Now()),12,31));

                     

                    Date:

                    LOAD date($(vMinDate)+IterNo()-1) as MaDate_Pres

                    AUTOGENERATE (1) WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

                     

                    Calendar:

                    LOAD

                    MaDate_Pres  as Date_Pres,

                    Year(MaDate_Pres) as Année_Pres,

                    'T ' & Num(Ceil(Month(MaDate_Pres)/3),'(ROM)0') as Trimestre_Pres,

                    Month(MaDate_Pres) as Mois_Pres,

                    MonthEnd(MaDate_Pres) as DateFinMois_Pres,

                    Week(MaDate_Pres) as Semaine_Pres,

                    Weekday(MaDate_Pres) as JourSemaine_Pres,

                    Day(MaDate_Pres) as Jour_Pres

                    RESIDENT Date;

                    DROP TABLE Date;

                     

                    DATA_BASE:

                    load DATE(DATE#(entry_date,'DD/MM/YYYY'),'DD/MM/YYYY') as entry_date, DATE(DATE#(exit_date,'DD/MM/YYYY'),'DD/MM/YYYY') as exit_date inline [

                    id_folders, entry_date, exit_date

                    123, 01/01/2018, 25/01/2018

                    124,15/12/2017, 09/01/2018

                    125,20/01/2018,14/02/2018

                    152,16/01/2018,16/01/2018]; // this is the line I added, to have one FLAG=1

                     

                    Flag_Date:

                    load *,

                    if(entry_date = exit_date,1,0) as Flag_Enty

                    //if(date(DATE_SORTIE<>DATE_SORTIE),0,1) as Flag_Exit,

                    //'1' as Research

                    Resident DATA_BASE;

                     

                    inner join(Flag_Date)

                    intervalmatch(Date_Pres)

                    load distinct

                    entry_date,

                    exit_date

                     

                    Resident Flag_Date;

                    DROP Table DATA_BASE;

                     

                    I just used the Date# and Date functions to evaluate and format the dates, and I added a line where Entry and exit date are equal.

                     

                    PFA

                      • Re: if match : Confirm function :
                        Axel Beaugrand

                        Hi Youssef,

                         

                        You're action is working, but I need to calculate  :

                        the numbers of entry

                        the numbers of exit

                         

                        I don't understand why you use this code :

                        if(entry_date = exit_date,1,0) as Flag_Enty : please could you clarify this point ?

                         

                        How I can calculate the numbers of exit ?

                        For you this code is ok :

                        if(exit_date = Date_Pres,1,0) as Flag_Exit

                         

                        And I need to know the personn who's entry, I tried to insert the id_folders but QV doesn't accept :S

                         

                        Many thanks for your help Youssef,

                        A.

                          • Re: if match : Confirm function :
                            youssef belloum

                            Alex, I can't follow you..

                             

                            Maybe it was a mistake using exit_date instead of present_date ??

                             

                            and what do you mean by calculate the number of entry ? and the number of exit ?

                             

                            can you show us the expected output of these calculations with the Hard-coded data above ?

                              • Re: if match : Confirm function :
                                Axel Beaugrand

                                Hi Youssef,

                                Many thanks for your help, please see below what I want in Hard-coded data :

                                In each date of stay I would like to match 1

                                Ex :

                                Stay:

                                load* inline [

                                Id_Clients, Entry_dat, Exit_Date

                                123, 01/01/20117, 25/01/2017

                                124,15/12/2016, 09/01/2017

                                125,20/01/201,14/02/2017];

                                 

                                Master_Calendar_Match_Date:

                                Date,

                                Id_customers,

                                Entry,

                                Exit

                                resident Calendar;

                                 

                                Please see attached the results of the table in .xls file (two solution page 1 and page 2):

                                I'm not sure what's is the best for QV and the best in term of calcul : page 1 or 2..

                                 

                                 

                                In this example, the end of my calendar is 14-02-2017, it means we are the 14-02-2017...

                                 

                                Thanks a lot for your help Youssef !

                                A.