Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to do some calculations on data and cannot have null values.
The original data set contains: Part, Date, & Qty
But not every part was sold on every date. So I split the data into two tables: one containing all possible parts the other containing all possible dates.
Now how can I create a table that contains both?
Pseudo code below:
Data:
Load
Part & '|' & Date as Part|Date,
Part,
Date,
Qty;
Table A:
Load Distinct
Part
Resident Data;
Noconcatenate
Table B:
Load Distinct
Date,
0 as ZeroQty
Resident Data;
Help needed
Table C:
Load
Part|Date
ZeroQty;
Left Join(Table C)
Load
Part|Date,
Part,
Date,
Qty;
Resident
Data;
Drop Table Data;
Noconcatenate
Final Result:
Load
Part|Date,
Part,
Date,
ZeroQty + Qty as TotalQty
Resident
Table C;
Drop Table C;
Do you have some sample row of data with your expected output? Not really sure what you are trying to do here
Start | ||
Part | Date | Qty |
A | 4/1/2016 | 3 |
A | 4/3/2016 | 1 |
B | 4/3/2016 | 6 |
Desired Output | ||
Part | Date | Qty |
A | 4/1/2016 | 3 |
A | 4/2/2016 | 0 |
A | 4/3/2016 | 1 |
B | 4/1/2016 | 0 |
B | 4/2/2016 | 0 |
B | 4/3/2016 | 6 |
Check this out:
Table:
LOAD * Inline [
Part, Date, Qty
A, 4/1/2016, 3
A, 4/3/2016, 1
B, 4/3/2016, 6
];
MinMax:
LOAD Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Table;
LET vMin = Peek('MinDate');
LET vMax = Peek('MaxDate');
DROP Table MinMax;
Join(Table)
LOAD DISTINCT Date,
Part;
LOAD Date($(vMin) + IterNo() - 1) as Date,
Part
Resident Table
While $(vMin) + IterNo() - 1 <= $(vMax);
FinalTable:
NoConcatenate
LOAD Part,
Date,
Alt(Qty, 0) as Qty
Resident Table;
DROP Table Table;
hi Scott,
let me know if Sunny's solution solved your purpose. Otherwise we can help you .
I apologize, I discovered an error in my data set. The dates aren't always consecutive.
Please see Below:
Input
Part | Date | Qty |
A | 1/1/2016 | 3 |
A | 2/1/2016 | 1 |
A | 3/1/2016 | 1 |
B | 3/1/2016 | 6 |
B | 4/1/2016 | 3 |
Output
Part | Date | Qty |
A | 1/1/2016 | 3 |
A | 2/1/2016 | 1 |
A | 3/1/2016 | 1 |
A | 4/1/2016 | 0 |
B | 1/1/2016 | 0 |
B | 2/1/2016 | 0 |
B | 3/1/2016 | 6 |
B | 4/1/2016 | 1 |
Not sure if you date is DD/MM/YYYY or MM/DD/YYYY, but assuming its the later you can try this:
Table:
LOAD * Inline [
Part, Date, Qty
A, 1/1/2016, 3
A, 2/1/2016, 1
A, 3/1/2016, 1
B, 3/1/2016, 6
B, 4/1/2016, 3
];
MinMax:
LOAD Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Table;
LET vMin = Peek('MinDate');
LET vMax = Peek('MaxDate');
DROP Table MinMax;
Join(Table)
LOAD DISTINCT Date,
Part;
LOAD Date(AddMonths($(vMin), IterNo() - 1)) as Date,
Part
Resident Table
While AddMonths($(vMin), IterNo() - 1) <= $(vMax);
FinalTable:
NoConcatenate
LOAD Part,
Date,
Alt(Qty, 0) as Qty
Resident Table;
DROP Table Table;
I apologize, I discovered an error in my data set. The dates aren't always consecutive.
I added a new data set below. I don't want to rely on generating dates. I would like it to use whatever values exist. The same process should result in the following:
Input
Part | Attribute | Qty |
A | E | 3 |
A | F | 1 |
A | G | 1 |
B | G | 6 |
B | H | 3 |
Output
Part | Attribute | Qty |
A | E | 3 |
A | F | 1 |
A | G | 1 |
A | H | 0 |
B | E | 0 |
B | F | 0 |
B | G | 6 |
B | H | 3 |
Hi Scott,
Got confused.. if i see your output data combination of Pat A and Attribute H is not there in your input.. how could your output will show.
What about this:
Table:
LOAD * Inline [
Part, Attribute, Qty
A, E, 3
A, F, 1
A, G, 1
B, G, 6
B, H, 3
];
Join(Table)
LOAD Distinct Attribute as NewAttribute
Resident Table;
NewTable:
LOAD Part,
If(Attribute = NewAttribute, Attribute, NewAttribute) as Attribute,
If(Attribute = NewAttribute, Qty, 0) as Qty
Resident Table;
FinalTable:
NoConcatenate
LOAD *
Resident NewTable
Where Peek('Attribute') <> Attribute
Order By Part, Attribute, Qty desc;
DROP Tables Table, NewTable;