Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using data from other table in Pivot table

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

4 Replies
sunny_talwar

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

Not applicable
Author

I don't know what you need.

Made some pictures:

This is table one:

Table1.jpg

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

Table2.jpg

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

Formula.jpg

Anonymous
Not applicable
Author

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

Not applicable
Author

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.