Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a query, I have a table which is shown below:
Input:
Row_num | Text |
1 | Jan |
2 | Feb |
1 | adr |
1 | asd |
2 | wer |
3 | Dec |
4 | Mon |
Here I want the unique value based first in first out on the Row_num fields:
Output:
Row_num | Text |
1 | Jan |
2 | Feb |
3 | Dec |
4 | Mon |
Thanks & Regards,
Pawan
TesT:
LOAD * INLINE [
Row_num, Text1,Text2,Text3,Text4,Text5
1, a1,b1,c1,d1,e1
2, a2,b2,c2,d2,e2
3, a3,b3,c3,d3,e3
4, a4,b4,c4,d4,e4
];
Left Join
LOAD * INLINE [
Row_num, Text6,Text7,Text8,Text9,Text10
1, Jan,Feb,Mar,Apr,May
2, Feb,Mon,Tue,Sat,Sun
3, adr,Fri,Qrt,wer,tyh
4, asd,sdf,wer,dsf,try
2, wer,ert,try,dfg,ert
3, Dec,dfg,rty,sdf,ghg
4, Mon,tyu,sdf,ert,fdg
];
NoConcatenate
Load * Resident TesT Where Peek(Row_num)<>Row_num;
Drop Table TesT;
May be this:
Table:
LOAD *
Where NOT Exists(Row_num);
LOAD * INLINE [
Row_num, Text
1, Jan
2, Feb
1, adr
1, asd
2, wer
3, Dec
4, Mon
];
Or you can try this as well.
Table:
LOAD Row_num, FirstValue(Text) as Text Group by Row_num;
LOAD * INLINE [
Row_num, Text
1, Jan
2, Feb
1, adr
1, asd
2, wer
3, Dec
4, Mon
];
Hi Both,
Thanks for your answer. Which is correct but not in my case.
I think I was wrong to explaining the question. Let me explain it fully:
Previously I was creating the report in excel and fetch the data from one table to another table using Vlookup function so I will get the first record from another table (if there is any duplicate records in another table).
But in Qlikview I am using left join so in that case I am getting multiple result because there is multiple entries in the right table (logically it is correct) but I want one record only which is comes first in the Right table. So here is an example:
LOAD * INLINE [
Row_num, Text1,Text2,Text3,Text4,Text5
1, a1,b1,c1,d1,e1
2, a2,b2,c2,d2,e2
3, a3,b3,c3,d3,e3
4, a4,b4,c4,d4,e4
];
Left Join
LOAD * INLINE [
Row_num, Text6,Text7,Text8,Text9,Text10
1, Jan,Feb,Mar,Apr,May
2, Feb,Mon,Tue,Sat,Sun
3, adr,Fri,Qrt,wer,tyh
4, asd,sdf,wer,dsf,try
2, wer,ert,try,dfg,ert
3, Dec,dfg,rty,sdf,ghg
4, Mon,tyu,sdf,ert,fdg
];
Here I want all the fields from both the table.
Please let me know if you have any concern.
Thanks & Regards,
Pawan
Hi Pawan,
Try this,
Table:
LOAD * INLINE [
Row_num, Text6,Text7,Text8,Text9,Text10
1, Jan,Feb,Mar,Apr,May
2, Feb,Mon,Tue,Sat,Sun
3, adr,Fri,Qrt,wer,tyh
4, asd,sdf,wer,dsf,try
2, wer,ert,try,dfg,ert
3, Dec,dfg,rty,sdf,ghg
4, Mon,tyu,sdf,ert,fdg
]
Where NOT Exists(Row_num);
Right Join (Table)
LOAD * INLINE [
Row_num, Text1,Text2,Text3,Text4,Text5
1, a1,b1,c1,d1,e1
2, a2,b2,c2,d2,e2
3, a3,b3,c3,d3,e3
4, a4,b4,c4,d4,e4
];
TesT:
LOAD * INLINE [
Row_num, Text1,Text2,Text3,Text4,Text5
1, a1,b1,c1,d1,e1
2, a2,b2,c2,d2,e2
3, a3,b3,c3,d3,e3
4, a4,b4,c4,d4,e4
];
Left Join
LOAD * INLINE [
Row_num, Text6,Text7,Text8,Text9,Text10
1, Jan,Feb,Mar,Apr,May
2, Feb,Mon,Tue,Sat,Sun
3, adr,Fri,Qrt,wer,tyh
4, asd,sdf,wer,dsf,try
2, wer,ert,try,dfg,ert
3, Dec,dfg,rty,sdf,ghg
4, Mon,tyu,sdf,ert,fdg
];
NoConcatenate
Load * Resident TesT Where Peek(Row_num)<>Row_num;
Drop Table TesT;
Both the solutions are work exactly as I want.
Thanks for giving the solution.
Regards,
Pawan