topic Re: Is this even possible? in QlikView

Is this even possible?

johanfo2 — Mon, 03 Sep 2012 08:18:47 GMT

I really don't know how to search the forums for this. I believe what I try to do here is beyond the simple query.

Lets say you have a fact table:

Sender Receiver

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

A B

A C

B A

C A

(etc)

Now, I want to display how many times A sends to B. This is simple:

Create a 2 dim table, and add a expression: count(Sender)

This wil give you something like this:

Sender Receiver Count

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

A B 2

A C 1

B A 1

C A 2

Now, here is the real question! Is it even possible to add a 4th column, which shows the reverse. That is how many time the receiver have sendt to the sender? What I want is this:

Sender Receiver Count CountReverse

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

A B 2 1

A C 1 2

B A 1 2

C A 2 1

But unfortunately, I haven't even been close to find an expression that creates this 4th colum?

Any ideas are greatly appreciated.

Re: Is this even possible?

thomas_skariah — Mon, 03 Sep 2012 08:42:22 GMT

Hi Johan,

Try the attached application.

Hope it will work for you.

Regards,

Tom

Re: Is this even possible?

johanfo2 — Mon, 03 Sep 2012 09:22:31 GMT

Tom,

If I understand your attached sample correctly, you just create two different tables, where you change the dimentions and do a regular count. Further, I didn't see the purpose of doing the concatenation of sender and receiver in the example. Am I missing something?

My problem is that I would like to have both "counter columns" in the same table.

Re: Is this even possible?

Mon, 03 Sep 2012 11:13:13 GMT

Hi,

I did as you specified, please check the attached file.

Thanks,

Niranjan M

Re: Is this even possible?

johanfo2 — Tue, 04 Sep 2012 09:23:15 GMT

This was helpful, and will enable me to solve my problem. However, optimally, I would like a solution to this that doesn't require load script logic and extra tables. Maybe am asking to too much ?

Re: Is this even possible?

johanfo2 — Tue, 04 Sep 2012 12:37:07 GMT

Based on previous suggestions, I solved it the following way:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='₹ #,##0.00;₹ -#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Data:
Load
          Sender As Sendero,
          Receiver As Receivero,
          Sender & '_' & Receiver As DirectRelation,
          Receiver & '_' & Sender As BackRelation,
          Id
;
LOAD * INLINE [
    Sender, Receiver, Id
    A, B, 1
    A, B, 2
    A, C, 3
    B, A, 4
    C, A, 5
    C, A, 6
    A, B, 7
    A, D, 8
];
Temp:
LOAD
          DirectRelation as DirectRelation,
          count(DirectRelation) As dcount
Resident Data
group by DirectRelation;
Concatenate (Temp)
LOAD
          BackRelation as DirectRelation,
          count(BackRelation) As bcount
Resident Data
group by BackRelation;

Further, I created a chart with 2 dimensions (sender , receiver) and two expressions 1) sum(dcount) 2) sum(bcount)

Re: Is this even possible?

johanfo2 — Thu, 06 Sep 2012 07:36:09 GMT

This is an even better solution, so I posted it aswell (see below). However, now I have encountered a new problem:

- If I in addion to the Sender, Receiver fields have a "Year" field, and when I select a year, I only want to count the backrelations in that year. Is this possible? It seems like my solution below is very static in the sense that i will not allow such a selection.

Data:
Load
          Sender As Sendero,
          Receiver As Receivero,
          Sender & '_' & Receiver As DirectRelation,
          Receiver & '_' & Sender As BackRelation,
          Id
;
LOAD * INLINE [
    Sender, Receiver, Id
    A, B, 1
    A, B, 2
    A, C, 3
    B, A, 4
    C, A, 5
    C, A, 6
    A, B, 7
    A, D, 8
];
left join (Data)
LOAD
          BackRelation as DirectRelation,
          count(BackRelation) as BackCount
Resident Data
Group by BackRelation;

Re: Is this even possible?

johanfo2 — Tue, 18 Sep 2012 13:50:29 GMT

I'm posting my final solution! I had to work quite a bit, but this was how I wanted it to be.

In this example, the default is to count all the backrelations accross all dates. If you select date(s), it will only count the backrelations within those dates.

The key was to use two tables, and this expression: =count({<Date2=P(Date)>}Date2)

Arriving at the solution was a mess, and I'm not even completely confident of how everyting works 😕 , yet it does. If anybody knows an even simpler way, please let me know.

Thanks to all who contributed with ideas and samples.