topic Re: connecting two tables in QlikView

connecting two tables

Fri, 14 Sep 2012 12:57:56 GMT

Hello,

i got this in my script:

Visitors:

LOAD [Datum posjete] as [Visitors datum], 1

[Broj posjetitelja], 2

Shop as Shopovi, 3

Mjesec as [Visitors mjesec] 4

FROM

Y:\Shopovi\Analiza\Visitors.xlsx

(ooxml, embedded labels);

This table has Visit Date (1), number of Visitors (2), in which Shop (3) and in which month(4). All Visitors for all Shops, per Day and month.

The Goal is to see for every shop turnover, number of invoices... AND the number of visitors on that day for this shop.

Table 1 attachment shows my "Table view". The calculated values i get from the table "Linije", "Visitors" is attached to "Shopovi" so that the name of the Shop is the unique key(Maybe already here is an error, i do not know). I Also need an "connection" between Visitors Datum and InvoiceDate(in table Linije), with an Loop, it gives me wrong numbers and the visitors need to be connected to shopovi!

Table 2 attachment shows the table i want it to look like. Now the "problem" is, all the calculated values/figures behind the field "Broj p..." show me the total amount for every shop. That is wrong, i would like that it shows me the values only for that day.

I would appreciate any help on following things:

- How can i connect "InvoiceDate" and "Visitors Datum", at least for the calculations(sum(Lineamount))... so that table 2looks like this, but with rigth figures?

- How has an expression to look like if i need two fields "checked" for the right Value? sum(if(InvoiceDate=Visitors Datum and(if(Shopovi=?,Lineamount)

Thank you very much in advance and i hope somebody can help me.

Re: connecting two tables

chematos — Mon, 17 Sep 2012 15:12:20 GMT

Use aggr() function to do the calculations by day or whatever you want, aggr() is like a group by so you could use something like this:

aggr( sum(whatever), Date)

This will make a sum by day, you can add more dimensions to group by them.

To work with dates, the better is to have a Master Calendar, there are a lot of examples to create one and use dates.

Hope this helps

Re: connecting two tables

Tue, 18 Sep 2012 07:10:41 GMT

Thank you for your answer. The Mastercalendar is a nice feature, didnt know about it.

Unfortunately i cant get it to work how i need. Seems i have to try a little bit more. But thank you.

Re: connecting two tables

Tue, 18 Sep 2012 07:53:51 GMT

Maybe someone can help me now. Attached youll see the tables(table viewer) i work with...

My Chart should look like this:

(DIMENSON):ShopName - Date(InvoiceDate)

(EXPRESSION):- Turnover - Nr. invoices.. - Nr. of Visitors(sum(br. posjetitelja)

Now the problem:

sum(Nr. Visitors) shows me all visitors for this shop, not for Date and Shop. F.ex. Shop XY worked 3 days, showing for every day Turnover and Nr. invoices, but Visitors are cumulative.

I want that Nr. of Visitors will be shown for every day, not cumulative the whole file.

How can i achieve that? What i do need to connect? How does the expression has to look like? I tried sum(if(InvoiceDate=VisitorsDate and Shopovi) Broj posj.), but it doesnt show anything.

I hope its now clear and somebody can help me .

Ty,

Kristian