topic Re: GENERATE ROWS in QlikView

GENERATE ROWS

Micki — Wed, 24 Mar 2021 10:09:58 GMT

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

Re: GENERATE ROWS

Or — Wed, 24 Mar 2021 11:09:30 GMT

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

Re: GENERATE ROWS

Micki — Wed, 24 Mar 2021 11:31:34 GMT

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

Re: GENERATE ROWS

Or — Wed, 24 Mar 2021 11:42:26 GMT

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;