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.
darrin_pilkingt
Contributor II

How two records can link to one value, like a package. Possibly link table?

I have customers that have a plan and features.  If they have just the plan then is is in that package.  If they have a plan and feature then it's in a different package.  This cannot be calculated in the load script as I need to be able to select a date and it shows what the customer had at any time.

As you can see below, if the customer just has a plan they they are in one package but if they have both a plan and feature they fall into a combined package.

I am wondering if some sort of link table between the Customer SKUs and the packages but not sure how this would work.

Notice below how customer starts with plan abc and feature 123 then changes to feature 456 and finally drops feature 456.  At any date selection during those periods I would need to see the Package they had.

Example:

CustomerTable

Customer    SKU    Type              StartDt          EndDt

001              abc    Plan              6/1/2015          7/25/2015

001              123    Feature          6/1/2015          6/20/2015

001              456    Feature          6/21/2015        7/15/2015

002              abc    Plan              5/1/2015          7/10/2015

003              efg      Plan              4/2/2015          7/23/2015

003              123    Feature          4/2/2015          7/23/2015

PackageTable

SKU                Package

abc                50

efg                100

(abc + 123)    Simple 50

(abc + 456)    Simple 100

(efg + 123)    Advanced 50

Thank you very much for your help.

5 Replies
darrin_pilkingt
Contributor II

Re: How two records can link to one value, like a package. Possibly link table?

I do not want the dates to scare anyone.  I have that portion setup.  I just wanted to show that this could not be set as a one time grouping and needs to be linked somehow. 

Basically the Plan and Feature SKUs on two rows.  If a customer has this it links to the appropriate group.  If only the Plan SKU then it links the the appropriate group.

Thank you.

darrin_pilkingt
Contributor II

Re: How two records can link to one value, like a package. Possibly link table?

Maybe this will better explain what I am trying to do.

If a customer has the 1 SKU they are in a group but if they have both the Plan and Feature SKUs they are in a combined group.


This needs to be done in the model as this needs to be dynamic.

grouping.jpg

Thank you for your help.

ramoncova06
Valued Contributor III

Re: How two records can link to one value, like a package. Possibly link table?

I know this not what you want to hear but the best option is to do this in the backend, you need to concatenate the sku for each customer and get what date it changed by combining result with intervalmatch

darrin_pilkingt
Contributor II

Re: How two records can link to one value, like a package. Possibly link table?

Yeah Ramon,

Not what i was hoping for.  I am guessing it would look something like this.

grouping2.jpg

darrin_pilkingt
Contributor II

Re: How two records can link to one value, like a package. Possibly link table?

Ramon,

Could you expand on this a little.  I am not sure I understand what you mean.

I created a date intervalmatch table on each Plan and then joined the features back into the bridge table to then concatenate the Plan and Feature SKUs together on the individual dates they match on and it is massive.  A 2gig + file.

Ramon Covarrubias wrote:

I know this not what you want to hear but the best option is to do this in the backend, you need to concatenate the sku for each customer and get what date it changed by combining result with intervalmatch

Community Browser