Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have following Sample data and I want to load the field 'Business' if it has Ind ='N'. i.e. even if ind = 'Y' at once, we wont load that 'business' field.
to illustrate, please refer below table, here we will load data w.r.t. business - CARAMEL APIERS only as it has all the ind = N.
Please refer attached QVW document. I dont want to do it in SQL preferable as my SQL query is already very complex as it has these fields from diff tables.
Please let me know if we have any way to handle it in load script itself.
No | Business | Ind |
---|---|---|
1 | MARUTI BULK | Y |
2 | MARUTI BULK | Y |
3 | MARUTI BULK | Y |
4 | MARUTI BULK | Y |
5 | MARUTI BULK | N |
6 | MARUTI BULK | N |
7 | CARAMEL APIERS | N |
8 | CARAMEL APIERS | N |
Directory;
Tab1:
LOAD * INLINE [
No, Business, Ind
1, MARUTI BULK, Y
2, MARUTI BULK, Y
3, MARUTI BULK, Y
4, MARUTI BULK, Y
5, MARUTI BULK, N
6, MARUTI BULK, N
7, CARAMEL APIERS, N
8, CARAMEL APIERS, N
9, CARAMEL APIERS, N
10, CARAMEL APIERS, N
11, CARAMEL APIERS, N
12, CARAMEL APIERS, N
13, CARAMEL APIERS, N
14, CARAMEL APIERS, N
15, PREFER BAGS, Y
16, PREFER BAGS, Y
17, PREFER BAGS, N
18, PREFER BINS, Y
19, PREFER BINS, Y
20, PREFER BULK, N
21, PREFER BULK, N
22, PREFER BULK, N
23, PREFER BULK, N
];
Join
load
Business,
Concat(DISTINCT Ind) as Flag
Resident Tab1 Group By Business;Final:
NoConcatenate
Load
*
Resident Tab1 Where not Index(Flag, 'Y');Drop Table Tab1;
Drop Field Flag;
may be like this at script?
Load
No,
Business,
Ind
From Tablename
where Ind='N';
//It will load only 'N' data
Check this?
Directory;
Tab1:
LOAD * INLINE [
No, Business, Ind
1, MARUTI BULK, Y
2, MARUTI BULK, Y
3, MARUTI BULK, Y
4, MARUTI BULK, Y
5, MARUTI BULK, N
6, MARUTI BULK, N
7, CARAMEL APIERS, N
8, CARAMEL APIERS, N
9, CARAMEL APIERS, N
10, CARAMEL APIERS, N
11, CARAMEL APIERS, N
12, CARAMEL APIERS, N
13, CARAMEL APIERS, N
14, CARAMEL APIERS, N
15, PREFER BAGS, Y
16, PREFER BAGS, Y
17, PREFER BAGS, N
18, PREFER BINS, Y
19, PREFER BINS, Y
20, PREFER BULK, N
21, PREFER BULK, N
22, PREFER BULK, N
23, PREFER BULK, N
];
Join
load
Business,
Concat(DISTINCT Ind) as Flag
Resident Tab1 Group By Business;Final:
NoConcatenate
Load
*
Resident Tab1 Where not Index(Flag, 'Y');Drop Table Tab1;
Drop Field Flag;
Hello!
Try this !
Thanks - Your answer is also 100% correct. But unfortunately I cant mark 2 correct answers. Hence marking as helpful!!
Thanks again!!!