Help with SQL

[hobanwashburne]

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;

[sunny_talwar]

Do you have some sample row of data with your expected output? Not really sure what you are trying to do here

[hobanwashburne]

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

[sunny_talwar]

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;

[Anonymous]

hi Scott,

let me know if Sunny's solution solved your purpose. Otherwise we can help you .

[hobanwashburne]

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

[sunny_talwar]

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;

[hobanwashburne]

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

[Anonymous]

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.

[sunny_talwar]

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;