topic Re: SQL statement in QlikView

SQL statement

Mon, 06 Oct 2014 11:46:24 GMT

Hi All,

I have 2 tables like below, i want to get record based on latest date. I have to write this in script to load data from SQL.

tab1:
F1	F2	F3
1	01/01/2014	test1
2	02/01/2014	test2
2	03/01/2014	test3
2	04/01/2014	test4
3	05/01/2014	test5
3	06/01/2014	test6
3	07/01/2014	test7
3	08/01/2014	test8
4	09/01/2014	test9
4	10/01/2014	test10
5	11/01/2014	test11

Tab 2
F1	F2
1	Name1
2	Name2
3	Name3
4	Name4
5	Name5

Final
F1	F2	F3	F4
1	01/01/2014	test1	Name1
2	04/01/2014	test4	Name2
3	08/01/2014	test7	Name3
4	10/01/2014	test10	Name4
5	11/01/2014	test11	Name5

Can any one help?

Re: SQL statement

anbu1984 — Mon, 06 Oct 2014 11:51:55 GMT

Tab1:

Load F1,FirstSortedValue(F2,-F2) As F2,FirstSortedValue(F3,-F2) As F3 Group by F1;

Load F1,Date#(F2,'D/M/YYYY') As F2,F3 Inline [

F1,F2,F3

1,01/01/2014,test1

2,02/01/2014,test2

2,03/01/2014,test3

2,04/01/2014,test4

3,05/01/2014,test5

3,06/01/2014,test6

3,07/01/2014,test7

3,08/01/2014,test8

4,09/01/2014,test9

4,10/01/2014,test10

5,11/01/2014,test11 ];

Join

Tab2:

Load * Inline [

F1,F4

1,Name1

2,Name2

3,Name3

4,Name4

5,Name5 ];

Re: SQL statement

alexandros17 — Mon, 06 Oct 2014 11:53:57 GMT

There are several ways to solve the problem, via script or via objects expression, I suggest via script:

Load * resident tab1;

left join

Load max(F2) as F2, '1' as flagMax resident tab1 group by F1;

in this way you will have a table with a new field flagMax to be used in your expression

Do now:

left join

load * resident tab2

now you have added Names.

Your expression will use the flag to show only max values

Let me know

Re: SQL statement

Mon, 06 Oct 2014 12:05:28 GMT

try this

Tab1:

Load

F1,

FirstSortedValue(F2,-F2) As F2,

FirstSortedValue(F3,-F2) As F3

Group by F1;

Load

F1,

date(Date#(F2,'D/M/YYYY')) As F2,

F3 Inline [

F1,F2,F3

1,01/01/2014,test1

2,02/01/2014,test2

2,03/01/2014,test3

2,04/01/2014,test4

3,05/01/2014,test5

3,06/01/2014,test6

3,07/01/2014,test7

3,08/01/2014,test8

4,09/01/2014,test9

4,10/01/2014,test10

5,11/01/2014,test11 ];

Join(Tab1)

Tab2:

Load * Inline [

F1,F4

1,Name1

2,Name2

3,Name3

4,Name4

5,Name5 ];

Re: SQL statement

maxgro — Mon, 06 Oct 2014 18:39:57 GMT

another solution

using peek to get only first F1 (order by F2 desc)

RESULT

SCRIPT

tab1:
load * inline [
F1, F2, F3
1, 01/01/2014, test1
2, 02/01/2014, test2
2, 03/01/2014, test3
2, 04/01/2014, test4
3, 05/01/2014, test5
3, 06/01/2014, test6
3, 07/01/2014, test7
3, 08/01/2014, test8
4, 09/01/2014, test9
4, 10/01/2014, test10
5, 11/01/2014, test11
];

final:
NoConcatenate load
  *
Resident
  tab1
where
  F1 <> peek(F1)
order by F1, F2 desc;

drop table tab1;

tab2:
load F1, F2 as F4 inline [
F1, F2
1, Name1
2, Name2
3, Name3
4, Name4
5, Name5
];