Automatic load unique entries of another table within autogenerate

[nougatitati]

Hi,

I have a data load script that is highly coupled to my current data set. I've recreated some sample code demonstrating my scenario:

findMinDate:
Load 
    Min([Date]) as [Earliest date]
Resident FullDataset;
Let vMin=peek('Earliest date', 0, 'findMinDate');

findMaxDate:
Load
    Max([Date]) as [Latest date]
Resident FullDataset;
Let vMax=peek('Latest date', 0, 'findMaxDate');

NoConcatenate MinutesPerDateAll:
Load
    [Subject],
    [Date]
Resident MinutesPerDate;
Join FullDateRange:
Load
    'Subject1' as [Subject],
    Timestamp('$(vMin)' + IterNo() - 1, 'DD/MM/YYYY') as [Date]
Autogenerate 1
While ('$(vMin)' + IterNo() - 1) <= $(vMax);
Join FullDateRange:
Load
    'Subject2' as [Subject],
    Timestamp('$(vMin)' + IterNo() - 1, 'DD/MM/YYYY') as [Date]
Autogenerate 1
While ('$(vMin)' + IterNo() - 1) <= $(vMax);
Join FullDateRange:
Load
    'Subject3' as [Subject],
    Timestamp('$(vMin)' + IterNo() - 1, 'DD/MM/YYYY') as [Date]
Autogenerate 1
While ('$(vMin)' + IterNo() - 1) <= $(vMax);
// ...
// ... and on and on for every Subject name in the [Subject] field
// ... until eventually
Join ExistingDateInfo:
Load
    [Subject],
    [Date],
    [Added time 1],
    [Added time 2],
    [Added time 3]
Resident MinutesPerDate;

This a lot of code to do some pretty simple stuff. Not only that, you can see it is tied explicitly to a data set with values in the [Subject] field of 'Subject1', 'Subject2', 'Subject3', etc. Is it possible to iterate over all the distinct values of [Subject] while also generating a date entry for every date between $(vMin) and $(vMax)?

[marcus_sommer · Accepted solution]

I must admit that I have difficulties to understand what do you want to do ... but just joining (distinct loaded) subject with the (per while) created date-list should do the same.

- Marcus

[nougatitati]

Wow that was simple; I didn't realise that joining tables with no common fields would create every combination of their fields possible. I'm glad it was such a simple solution. Thanks!