What feature to support multiple comaprison table structure.

[#1]

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.         

[swuehl]

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

[#3]

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.

[swuehl]

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

[#5]

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

[swuehl]

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

[#7]

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.

[swuehl]

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?

[#9]

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.

[swuehl]

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