topic Data Modeling w/ RangeDate in QlikView

Data Modeling w/ RangeDate

Fri, 15 Oct 2010 15:45:22 GMT

Hi there,

I'm having difficulty in reaching the following result of modeling.

Original Structure

ID	DT_COMPETENCIA	STATUS
1	01/01/2010	A
1	03/01/2010	B
1	05/01/2010	C
2	01/01/2010	A
2	04/01/2010	B
3	31/12/2009	A
3	02/01/2010	B
3	04/04/2010	C

Final Structure

ID	DT_COMPETENCIA	STATUS
1	01/01/2010	A
1	02/01/2010	A
1	03/01/2010	B
1	04/01/2010	B
1	05/01/2010	C
2	01/01/2010	A
2	02/01/2010	A
2	03/01/2010	A
2	04/01/2010	B
3	31/12/2009	A
3	01/01/2010	A
3	02/01/2010	B
3	03/01/2010	B
3	04/04/2010	C

Legend

*******	New records in Bold
NewRecords(Status) = Previous(Status)

	Range Date ID= 1
Min	01/01/2010
Max	05/01/2010

	Range Date ID= 2
Min	01/01/2010
Max	04/01/2010

	Range Date ID= 3
Min	31/12/2009
	04/04/2010

[;)]

Can anyone help me?

Thanks!!

Data Modeling w/ RangeDate

pover — Fri, 15 Oct 2010 16:11:21 GMT

It would be easier to create a new columna and use intervalmatch to link to a master calendar table. So using previous() or peek() you can get a table like the following:

ID	DT_COMPETENCIA TO_DATE	STATUS
1	01/01/2010 02/01/2010	A
1	03/01/2010 04/01/2010	B
1	05/01/2010 31/12/9999	C
2	01/01/2010 03/01/2010	A
2	04/01/2010 31/12/9999	B
3	31/12/2009 01/0/2010	A
3	02/01/2010 03/04/2010	B
3	04/04/2010 31/12/9999	C

Instead of 31/12/9999 you could put null() and use the command nullasvalue TO_DATE so that the range is infinite.

Then use the intervalmatch function to link to a master calendar or a table that has all the dates.

Regards.

Data Modeling w/ RangeDate

Fri, 15 Oct 2010 19:08:05 GMT

Hi Karl,

Thanks for your attention.

Sorry about my english

But I need to create a line for each date within the range

Min_Date = Date('01/01/2010','DD/MM/YYYY') >> Sales_Date

Max_Date = Date('03/01/2010','DD/MM/YYYY') >> Sales_Date

Something like that:

_______________

Original Structure_

ID DATE STATUS

1 01/01/2010 A

1 03/01/2010 A

2 03/01/2010 B

2 06/01/2010 A

_________________________________________

4 RECORDS

_____________

Final Structure_

ID DATE STATUS

1 01/01/2010 A

1 02/01/2010 A (New Record)

1 03/01/2010 A

2 03/01/2010 B

2 04/01/2010 B (New Record)

2 05/01/2010 B (New Record)

2 06/01/2010 A

_________________________________________

7 RECORDS

Best regards

Data Modeling w/ RangeDate

pover — Fri, 15 Oct 2010 20:18:06 GMT

Vitor,

I guess I don't understand why you need to create those extra rows when you can get the same end result using what I recommended as you can see in the attached file.

Regards.

Data Modeling w/ RangeDate

johnw — Fri, 15 Oct 2010 20:25:07 GMT

And if you really want all those extra rows, you can join the tables together at the end. I'm not sure there's a need, though.