topic Re: Using data from other table in Pivot table in QlikView

Using data from other table in Pivot table

Wed, 25 Nov 2020 16:16:04 GMT

I'm trying some stuff here and run into a challenge.

I have some SQL queries that working fine. As outcome I have two tables. One very short one whit only 2 rows, looks like:

Date,Amount

20160901,7

20160926,3

The other one is some bigger, also starting whit a Date and some other values that don't matter. The first date is the same in both tables.

Now I want to the amount of the lowest date from the first table in the second table at the first row in a new column. I made a new column whit an IF statement, but whit no success.

So I start whit

If (DateTable2=Min(DateTable1),

this is working so far, but how do I get the value "amount" belonging to the lowest date from table 1 into table 2? In this case the number 7.

When I do this, the cell stays empty, because "amount" has 2 values. This works fine if table 1 only has one row of data (also does not need the Min Function in that case).

If (DateTable2=Min(DateTable1),amount, (other calculating's that working already) )

Re: Using data from other table in Pivot table

sunny_talwar — Tue, 27 Sep 2016 17:05:05 GMT

Would you be able to share a sample to look at this?

Re: Using data from other table in Pivot table

Wed, 28 Sep 2016 07:32:51 GMT

I don't know what you need.

Made some pictures:

This is table one:

And this is table two, you see the 'here' that's were is want the number from table one from the column Menge (amount) where the date is the oldest. In this case the number 20

And this is the formula in the 'test' column:

Re: Using data from other table in Pivot table

Anonymous — Wed, 28 Sep 2016 07:48:39 GMT

may be something like this?

Table1:

Load

From Table1;

noconcatenate

Table11:

mapping //Use mapping load

load

Date,

min(amount) as Min_Amount

from Table1 group by Date;

drop table Table1;

Table2:

Load

Date,

Amount,

Applymap('Table11', Date, Min_Amount) as Min_Amount

from

Table2;

Use this Min_Amount Filed now...

Re: Using data from other table in Pivot table

Wed, 28 Sep 2016 09:26:59 GMT

That's a whole other way of what I was trying.

I made some pictures, see above. That already is the outcome of my SQL script. If necessary I could also post this.

Problem is that the server is a MS-SQL 2000 server.