10 Replies Latest reply: Aug 4, 2011 5:39 AM by Jeffrey Chan RSS

    What feature to support multiple comaprison table structure.

      Hi,

       

      i am new onboard.

      i have a senario which i am here to seek for help to achieve it. For example :

      I have 3 table

       

       

      Table Getin,     Table Getout,     Table Payment

      ----------------      ------------------     ---------------------

      EntranceNo     ExitNo                  Ticket No

      Ticket No          Ticket No             Amount

       

       

      in order for me to "Count" those same ticket no. in table Getin and Getout, therefore i need to compare the value from

      Table Getin,     Table Getout,  and   Table Payment  to obtain the same ticket number for generate the result like (Amount for the Ticket, Total paid ticket and total non-paid ticket.

       

      In sql server i can create view to compare those value and field, but in qlikview i wonder how can it can achieve?

       

      Thank you for time and i am still discovering the features of qlikview. Hope that i can learn in deep.         

        • What feature to support multiple comaprison table structure.
          Stefan Wühl

          Hi

           

          not sure if I understood you correctly, but QlikView will automatically associate fields with identical names (Ticket No in your case) between tables.

           

          So, after loading your three tables, a straight table chart with dimension Ticket No and expressions

          =count(EntranceNo)

          =count(ExitNo)

          =sum(Amount)

           

          should give you the information that I think you may want. (Well, I' am not sure how you define paid ticket and non paid tickets, above will just count the associated field values).

           

          Hope this helps,

          Stefan

            • Re: What feature to support multiple comaprison table structure.

              thx for your reply, swuehi.

               

              for example data i have :

              Table 1 (Enter)

              t1uid     EnNo          EnDate     TicketNo

              t101   e21             2011          1234

              t102   e31             2011           2345

              t103   e21             2010           9876

               

              Table 2 (Exit)

              t2uid     ExNo          ExDate     TicketNo

              t201     e31             2011           2345

              t202         -             2011           1234

              t203     e21             2010           9876

                    

              Table 3 (Payment)

              t3uid      TicketNo     Payment

              t301       2345            8

              t302       1234            -

              t303       9876             3

               

               

              those posibilities included

              1. Total count for EnNo(s) and ExNo(s) for Amount has paid. ---correct?

              if(Amount>=0),Cout(EntryNo) --- i hope to sum all count EnNo instead show one by one

               

              2. Total count for EnNo(s) and ExNo(s) for Amount non pay. ---correct?

              if(Amount<=0),Cout(EntryNo) --- i hope to sum all count ExNo instead show one by one

               

              3. Total EnNo and ExNo --- (1+2) the sum count value for step 1 add step 2 to get total

               

              4. Total no ExNo. ---correct?

              if(Count(ExNo<=0),Cout(EntryNo))

               

              5. Total EntryNo (3+4) --- (3+4) the sum count value for step 3 add step 4 to get total

               

              6. Total Exit. (+3) --- Total step 3

               

              7. Variance(+5-6) --- Total EntryNo - Total Exit No

               

              Conclusion

              Entry and Exit Paid Ticket = TicketNo in EntryTable must in ExitTable and The TicketNo in PaymentTable the Amount must > 0;

              Entry and Exit None Pay Ticket = TicketNo in EntryTable must in ExitTable and The TicketNo in PaymentTable the Amount must = 0;

              Total = Entry and Exit Paid Ticket + Entry and Exit None Pay Ticket;

               

               

              thanks anyway for your help, swuehi. Please asist me if i were wrong in concept.

               

               

                • Re: What feature to support multiple comaprison table structure.
                  Stefan Wühl

                  Hi Jeffrey,

                   

                  for conditional counting, I use e.g. for paid tickets:

                  =Count(if(Payment>0 and EnNo=ExNo ,EnNo) )

                   

                  i.e. put the if() inside the count().

                   

                  I have attached a small sample with your data. You will see that (in Table Viewer, call with CTRL-T) all tables are linked via TicketNo automatically.

                   

                  I was not sure if I understood your business logic correctly, but I think correcting me should be quite easy for you.

                   

                  Regards,

                  Stefan

                    • Re: What feature to support multiple comaprison table structure.

                      Hi,

                       

                      thx.

                       

                      let said now, the TicketNo value is in Table entry but not exist in Table exit. in other word i want to check how many ticket is not exit after taken... How could i achieve?

                      Expression in such way?

                       

                      TicketNo in EnNo is not in ExNo... how to specific field from which table..

                       

                      =Count(if(TicketNo<>TicketNo ,TicketNo) )

                       

                      how to make different in such way that i can check for matching ticket number in table entry and the table Exit. since the EnNo and ExNo is totally different.

                      Thanks

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                        • Re: What feature to support multiple comaprison table structure.
                          Stefan Wühl

                          Hi,

                           

                          I think you don't need to check for same ticket number, this will be done by QlikViews associating the table fields with name TicketNo already.

                           

                          I added some more line to illustrate your problem (please check in script that this was correct).

                           

                          I also added a simple count difference to tell the exited vs. the entered tickets.

                           

                          Hope this helps,

                          Stefan

                            • Re: What feature to support multiple comaprison table structure.

                              Hi,

                               

                              Here i am enclosing the edit version of your work which i have amend some data for you to refer.

                               

                              Sorry to said that the EnNo and ExNo is different in both table means the value of EnNo from TableEntry will not found in TableExit.

                               

                              Therefore, in order to count total entries paid and exit tickets, we cannot assume EnNo=ExNo.

                               

                              Besides that, do u have any idea about drill down level of showing content. For example, i wish to list out the ticketNo.which is non-pay... so only way is i generate another list item for showing that instaed double click on the total numbers non-pay to show the ticketNo list.

                               

                              For my information, can you suggest me some thread to show conversion DateToDay in Qlikview. For example, 8/3/2011 is Wednesday and also for DateTime Range.

                               

                              Billion thanks.

                                • Re: What feature to support multiple comaprison table structure.
                                  Stefan Wühl

                                  Total entries paid with also an exit entry could be:

                                  = 'Total Entries paid and Exit :  '& count(if(Amount>0 and isNull(EnNo) = 0 and isNull(ExNo)=0,TicketNo))

                                   

                                  Correct?

                                    • Re: What feature to support multiple comaprison table structure.

                                      Hi, Swuehi

                                       

                                      i affaid it will not work for million of record..? cause i found the result is not tele to the Count for EnNo or Ticket Number. and i check around the data source and i notice there is "-" (NULL) and "0" for payment means the value is different. Therefore, i try to enhance it with this way, but unfortunately the result not tele also :

                                       

                                      = 'Total Entries Paid & Exit :  '& count(if(Amount>=0 and isNull(EnNo) = 0 and isNull(ExNo)=0,TicketNo))

                                       

                                      = 'Total Entries & Exits Non Pay: ' & Count(if((IsNull(Amount)=-1 and IsNull(EnNo)=0 and IsNull(ExNo)=0)

                                      ,TicketNo) )

                                       

                                      ----------------------------------------------------------------------------

                                      after my investigation on the data source, i found that the ticketNo and Amount appear in Payment table twice which one record Amount is 0 and another is 3...i wonder how do i take this kind of record as only one for Total Entries and Exit and Paid.

                                       

                                       

                                      =count(Amount>=0 and isNull(EnNo)=0 and isnull(ExNo)=0,TicketNo))

                                       

                                      this will return me as 2 and the total entry is actually 1.

                                       

                                      Any wrong with my syntax or formula?

                                       

                                      thx again.

                                        • Re: What feature to support multiple comaprison table structure.
                                          Stefan Wühl

                                          Hi Jeffrey,

                                           

                                          why do you think it will not work for millions of records? Because of performance or do you think the results are getting incorrect?

                                           

                                          BTW, do you count Ticket No with amount of 0 as paid or unpaid? I assume unpaid, so you might want to check for amount > 0  for paid tickets.

                                           

                                          With your last expression:

                                          =count(Amount>=0 and isNull(EnNo)=0 and isnull(ExNo)=0,TicketNo))

                                           

                                          I think there is an if( missing at the beginning, this expression should not work.

                                          If you want every Ticket No only once, you could use count DISTINCT like

                                           

                                          =count(DISTINCT if(Amount>=0 and isNull(EnNo) = 0 and isNull(ExNo)=0,TicketNo))

                                           

                                          But with duplicate ticket no you will run into troubles, like having to entries in payment with zero and 3, will you count that ticket to paid or unpaid group? I guess you have to decide to also look for last date then.

                                           

                                          Would be good if you could rework your data sample to contain all possible business cases to take care of.

                                           

                                          If you want to automatically select all amounts with Null or <=0 value, you could use a button with an select in field action. I have attached a sample of how you could do that.

                                           

                                          Stefan

                                           


                                            • Re: What feature to support multiple comaprison table structure.

                                              Hi,

                                               

                                              Yes. for the duplicate ticket in Payment table i would like to look for latest datatime instead...i read thru the table and those duplicate record is start with 0 and then number.

                                              Because of the data source, it probably make me in trouble...

                                               

                                              Legend :

                                              - = null

                                              0 = paid (Free of charge)

                                               

                                              For example :

                                               

                                              ticketNo     Amount     pdatetime

                                              t1               0               4/27/11 3:00 PM

                                              t1               3              4/27/11 3:01 PM

                                               

                                              so i would to check for the greater time instead...

                                               

                                              it seen i have long path for me to get friend with Qlikview...