GENERATE ROWS

[Micki]

Hi folks, 

I want to generate data table using distinct column like 
I have two values in column  

Column1:

2020-01-02

2020-02-03

Result: 

Column 1       Column 2

2020-01-02    2020-01-02

2020-01-02    2020-02-03

2020-02-03    2020-02-03

2020-02-03    2020-01-02

And of course without cross join 

[Or]

Why without cross join? Based on your description, it sounds like the cross join is exactly what you want...

[Micki]

@Or , cross join takes too much time to generate it i have ~ 1 000 000 rows , that's why i am looking something different

[Or]

I think anything else you'd use, probably a loop, would only take longer, but I haven't actively tested this.

You could use a loop on FieldValueList and find out if it's faster, I guess...

Table1:
Load * INLINE [
Column1
2020-01-02
2020-02-03];

FOR Each i in FieldValueList('Column1')

Load Column1, '$(i)' as Column2
Resident Table1;

NEXT i

Drop table Table1;

You could also dual-loop on FieldValueList and skip reading from the resident entirely... Again, not sure if that would have a positive impact on performance or not - my guess is it'd be a negative one, but I haven't tested.

Table1:
Load * INLINE [
Column1
2020-01-02
2020-02-03];

FOR Each i in FieldValueList('Column1')
For each j in FieldValueList('Column1')
Load '$(j)' as Column1, '$(i)' as Column2
Autogenerate(1);
next j
NEXT i

Drop table Table1;