Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Group table, containing:
Dept, Flag
1,A
2,B
And Master Table containing:
SKU, Dept
S1,1
S2,1
S3,1
S4,2
S5,3
S6,3
And a Sales Table containing:
TRX, SKU
1, S1
1, S2
2, S1
2, S5
3, S3
3, S4
3, S5
4, S4
5, S6
5, S1
What I need to have a new Sales table having only transactions having their SKUs exclusively belonging to the Group table, (i.e transaction of items in Dept 1 & 2 exclusively). Transactions having items from dept 3 & any other dept are to be excluded. i.e: TRX number 1 only matches the criteria.
How to create this table using the script?
Surely all sales rows with SKU <= S4 should be included?
Dear Jason,
The condition is that the transaction should contain all its items belonging to department having a valid Flag in the Group table.
Thanks for your reply
Hi you could try somthing like:
[Master1]:
LOAD * INLINE [
SKU,Dept
S1,1
S2,1
S3,1
S4,2
S5,3
S6,3
];
Join ([Master1])
LOAD * INLINE [
Dept, Flag
1,A
2,B
];
Join ([Master1])
LOAD * INLINE [
TRX, SKU
1, S1
1, S2
2, S1
2, S5
3, S3
3, S4
3, S5
4, S4
5, S6
5, S1
];
Master:
Load '' as Q,
*
Resident Master1
Where Dept < '3';
Drop table Master1 ;
Drop Field Q from Master;
Or :
[Master1]:
LOAD * INLINE [
SKU,Dept
S1,1
S2,1
S3,1
S4,2
S5,3
S6,3
];
Join ([Master1])
LOAD * INLINE [
Dept, Flag
1,A
2,B
];
Join ([Master1])
LOAD * INLINE [
TRX, SKU
1, S1
1, S2
2, S1
2, S5
3, S3
3, S4
3, S5
4, S4
5, S6
5, S1
];
Master:
Load '' as Q,
*
Resident Master1
Where len(Flag) > '0';
Drop table Master1 ;
Drop Field Q from Master
The easiest way is to use left keep load:
Group:
LOAD
...;
//
Master:
LEFT KEEP (Group) LOAD
...;
//
Sales:
LEFT KEEP (Master) LOAD
...;
This last one only will load the data where the lenght of the field flag is smaller then '0' (so not exsistin)
That's what I thought. But your Master table has SKU values S1,S2,S3 and S4 all with Dept 1 or 2 which have valid flags in the Group table. So shouldn't all TRX values be include as they all have at least one record with a SKU value associated to a valid Dept? Or do you only want Sales rows where all TRX values are related to valid Depts?
Dennis, what if there are more than 3 departments...?
EDIT: I have re-read your response and you have answered my question already! Sorry...
Thanks Michael,
you are close. Attach is the desired result. TRX having items not in master, should be eliminated entirely.
Thus the result will be only for TRX no.1 & 4
We need an additional small step to reach this result.
Thanks Michael,
you are close. Attach is the desired result. TRX having items not in master, should be eliminated entirely.
Thus the result will be only for TRX no.1 & 4
We need an additional small step to reach this result.