Hi,

 I have got one table which contains sales records for each week:

Week  ProductName Value

wk1    ProdA    5

wk1   ProdB    3

wk2   ProdA   4

wk2  ProdC    5

If a certain product is not sold that week, it is not stored as a row for that week.

If I want to insert these non-sold product with 0 value, will I be able to do this?

 I can do this if the table only contains one week, using a full week and anti-join with the week and then append.

Can anyone think about a way of doing it for multiple weeks? Thanks.

Hi,

I think that you should create a table with all weeks (1 to 52) and all products (ProdA to ProdZ) (=> 2 fields)
filter Value's field from sales's file
merge (anti join, first file with new table)
merge (full outer join) merge + sales's file
filler (based on condition: @NULL(Value) => 0)
field reorder (Week / ProductName / Value)
sort (Week / ProductName)

you should use TRIM() function before to merge the fields because the sales's file are not "clean" (space)

Thank you very much DavidF, I think that is the solution .

Can I ask if it is some pattern you created yourself, or is there any tutorial or help file mentioned this method? It is quite useful so I want to find out more about it.

My summary of your full outer join is: out join can be used as append if there is no intersection
 

No tutorial, it was created just for you.

and for me to do exercise of course.

The method works if the artificial table created contains exactly the same weeks as the actual sales file.

 Which can be implemented easily by using Tim's cartisian method (one product list and another aggregation for week/year combination.

One alteration:

 Not out-join

but Append, otherwise you receive "duplicated fields" error.

my previous comment of "outer join has same effect as append" is wrong. 

"The method works if the artificial table created contains exactly the same weeks as the actual sales file"

of course, otherwise it's impossible to merge. that's why you shoud use the trim() function.

 if you don't know exactly the name of the future weeks, then just create an artificial table until the last week you use.

before to post, I used your data set and received no problem with merge / full outer join.

so, tell me the way to post my stream and I'll do it...

HI DavidF,

it is "options" tab in replying, there you can attach a file.

 I made it work with Append node, I don't understanding how you can merge missing records with non-missing records and make it a complete list.

haha, I think I know what DavidF means.

 Use one partial outer join only, the artificial table has only the key fields, so it only brings in the missing keys, other fields will be automatically created.

Found a simpler way:

 Create a complete list of every week and every product, then partial outer join with existing records.......