topic Re: What feature to support multiple comaprison table structure. in QlikView

What feature to support multiple comaprison table structure.

Fri, 20 Nov 2020 18:02:30 GMT

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.

swuehl — Sat, 30 Jul 2011 23:56:14 GMT

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.

Mon, 01 Aug 2011 03:05:49 GMT

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.

swuehl — Mon, 01 Aug 2011 09:11:55 GMT

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.

Tue, 02 Aug 2011 08:31:28 GMT

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.

swuehl — Tue, 02 Aug 2011 11:12:55 GMT

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.

Wed, 03 Aug 2011 04:10:38 GMT

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.

swuehl — Wed, 03 Aug 2011 07:07:35 GMT

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.

Thu, 04 Aug 2011 01:28:37 GMT

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.

swuehl — Thu, 04 Aug 2011 08:56:29 GMT

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.

Thu, 04 Aug 2011 09:39:35 GMT

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...