Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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
jsn
Honored Contributor

Re: How to do as of report for SCD?

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

Re: How to do as of report for SCD?

Hi Johannes,

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

jsn
Honored Contributor

Re: How to do as of report for SCD?

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

Re: How to do as of report for SCD?

Thanks will check that!

advait_thakur
Contributor III

Re: How to do as of report for SCD?

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

Not applicable

Re: How to do as of report for SCD?

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
Contributor

Re: How to do as of report for SCD?

Hi Bill,

Have you found a solution to your problem?

Please shared if you have th solution.

Best regards,

Emteam

Not applicable

Re: Re: How to do as of report for SCD?

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
Contributor

Re: How to do as of report for SCD?

Hi Bill,

Many thanks for your answer.

i will check it.

Best Regards,

Emteam

Community Browser