Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to do as of report for SCD?

Hi,

I already have the SDC or slowly changing dimension application, but my problem is, how can I show reports depending on selected date,

These are the data:

NameNoNameStatusMostRecentValidFromValidTill
10001BillMarried110/5/201210/5/2012
10001BillSingle010/3/201210/4/2012
10002ReaMarried110/5/201210/5/2012
10002ReaSingle010/3/201210/4/2012
10003LudySingle110/3/201210/5/2012
10004KhenIn Relationship110/5/201210/5/2012
10004KhenSingle010/3/201210/4/2012
10005LhaiIn Relationship110/5/201210/5/2012
10005LhaiSingle010/3/201210/4/2012
10006PyeIn Relationship110/5/201210/5/2012
10006PyeSingle010/3/201210/4/2012
10007MarkSingle110/5/201210/5/2012
10008BambooSingle110/5/201210/5/2012
10009JhingSingle110/5/201210/5/2012
10010ElizaIn Relationship110/5/201210/5/2012
10011KirkIn Relationship110/5/201210/5/2012
10012CharmIn Relationship110/5/201210/5/2012

I just don't know how can I show the status if I want to see employee status as of

1. Oct 3, 2012

2. Oct 4, 2012

3. Oct 5, 2012

Will you help me how to show the as of reports?

Thanks,

Bill

9 Replies
Anonymous
Not applicable
Author

Hi Bill,

Here it sounds to me like you could intervalmatch() your calendar to the ValidFrom - ValidTill fields so when a user picks Oct 3rd from the calendar it will match against the ValidFrom-ValidTill ranges that cover this date.

Not applicable
Author

Hi Johannes,

Do you have any idea how to do that? Will you help me doing intervalmatch? Thanks.

Anonymous
Not applicable
Author

Hey Bill,

Try searching the forums. Should be plenty of different examples floating around.

Here's one I found (see John W's post):

http://community.qlik.com/message/132251#132251

Not applicable
Author

Thanks will check that!

advait_thakur
Creator III
Creator III

Hello Bill,

Check out this

First load the two tables (Ex: Tabel 1 and Table 2) as usual at script area, then link the field (Common) to the intervals defined by the fields Valid from and Valid Till:

Select * from Table1;

Select * from Table2;

IntervalMatch ( Your common field) select Valid from, Valid Till from Table2;

I hope this will help you out, i have just highlightes the overview of intervalmatch.

Thanks

Advait

Join the official Qlik Enthusiast's page here
https://www.linkedin.com/groups/6513382/
Not applicable
Author

I have tried doing SCD in hundred thousand rows. It sucks! How can I do SCD having of that kind of records? Any suggestions?

emteam162
Creator
Creator

Hi Bill,

Have you found a solution to your problem?

Please shared if you have th solution.

Best regards,

Emteam

Not applicable
Author

Yup Farid, have the solution for this (but it's not really mine, I just modified or add some modification), I hope you can open the attach file, it's like what the actual requirement is.

emteam162
Creator
Creator

Hi Bill,

Many thanks for your answer.

i will check it.

Best Regards,

Emteam