Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Check validation dates before join

In an Excel-file validation dates are included to show which record should be used.

For example:

BusinessUnit     ; Cluster      ;  Account          ; Validation date from  ; Validation date to

BU 1                 ; Owner A    ; Company F      ; 01-01-2012               ; 01-01-2020

BU 2                 ; Owner B    ; Company G     ; 01-01-2012               ; 01-01-2020

BU 2                 ; Owner A    ; Company F     ; 09-09-2013               ; 01-01-2020

If you look at above example you can see that Account F has very recently been moved to another cluster.

The account along with forecastdata, actual (hour and volume) is gathered from textfiles.

AccountData:

LOAD      Account,

               [Actual Hours],

               [Actual Volumes],

               [Forecasted Hours],

               [Forecasted Volume]

FROM     $(TextFiles);

It the same way it's possible to load the BussinessUnit and Cluster into a seperate table:

BU:

LOAD     BusinessUnit,

              Cluster,

              Account,

              [Validation date from],

              [Validation date to]

FROM     '$(vDataFolder)BuCluster.xlsx'

Now I need help cleaning up this BU - table.

The account can only have one result in this table:

The one with the most recent [Validation date from]  and with a valid [Validation date to]

Help would be very much appreciated.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Check validation dates before join

Hi

I agree with tresesco, this is a slowly changing dimension. The appropriate way to handle this in QV, is to use an "extended interval match". For this to work, your account data needs a date field, to identify which BU and cluster to use for an account (as this is what changes over time).

Lets assume that AccountData has a date field, eg: Period (stored as a date)

Left Join (AccountData)

IntervalMatch (Period, Account)

LOAD [Validation date from],

     [Validation date to],

     Account

Resident BU;

This will create a syn table, which is OK, but if you would like to eliminate it, then:

Left Join (AccountData)

LOAD [Validation date from],

     [Validation date to],

     Account,

     BusinessUnit,

     Cluster

Resident BU;


DROP Fields [Validation date from], [Validation date to];

DROP Table BU;


Now the BusinessUnit and Cluster are correct for each [Period] in the AccountData table.


Hope that I understood your requirement and that this helps

Jonathan


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
3 Replies
MVP
MVP

Re: Check validation dates before join

I am not very sure if i have understood. May be link about Slowly changing dimension could help you.

somenathroy
Contributor III

Re: Check validation dates before join

You may try the below logic:

BU:

LOAD     BusinessUnit,

              Cluster,

              Account,

              [Validation date from],

              [Validation date to]

FROM     '$(vDataFolder)BuCluster.xlsx'

Right Join

LOAD     BusinessUnit,

              Cluster,

              Account,

              Max([Validation date from]) as [Validation date from]

FROM     '$(vDataFolder)BuCluster.xlsx'

Where [Validation date from] <= [Validation date to]

Group By

      BusinessUnit,

              Cluster,

              Account;

Regards,

som

MVP
MVP

Re: Check validation dates before join

Hi

I agree with tresesco, this is a slowly changing dimension. The appropriate way to handle this in QV, is to use an "extended interval match". For this to work, your account data needs a date field, to identify which BU and cluster to use for an account (as this is what changes over time).

Lets assume that AccountData has a date field, eg: Period (stored as a date)

Left Join (AccountData)

IntervalMatch (Period, Account)

LOAD [Validation date from],

     [Validation date to],

     Account

Resident BU;

This will create a syn table, which is OK, but if you would like to eliminate it, then:

Left Join (AccountData)

LOAD [Validation date from],

     [Validation date to],

     Account,

     BusinessUnit,

     Cluster

Resident BU;


DROP Fields [Validation date from], [Validation date to];

DROP Table BU;


Now the BusinessUnit and Cluster are correct for each [Period] in the AccountData table.


Hope that I understood your requirement and that this helps

Jonathan


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser