Need help matching multiple fields over an interval of time

[Anonymous]

Hello,

I have several support staff on my team and I'm trying to match their casework to our sales data to get an idea of the impact that they have on our overall sales.  I've been able to muddle through a lot of problems in qlikview, but I'm not sure how to approach this.

From the Casework Table [Fact]  I receive a list of up to 10 parts that were worked on a case. Each row contains the date the case was opened, the date the case was closed, the total case age, Case# , and the parts that were referenced in the case. There are up to 10 parts on a case and each one is loaded into qlikview in it's own column: Part1, Part2, Part3, etc.

In another table I have my Sales Data [Sales] with invoice date and part detail. Each row contains the Invoice date,  part#, and Sales dollars.

I'd like match the parts from the Casework Table [Fact]  to the Sales Data Table [Sales] if the part invoiced within two weeks of the case closing.

Will I need to match each part 1-10 to the sales data as separate expressions, or would I be better handling this another way? Thanks in advance for any guidance.

DJ

[sunny_talwar]

It doesn't seem to be very obvious, so I guess it might help to know the expected output in numerical terms. I mean it seems that Fact only have 2 rows of data and I am still finding it somewhat difficult to know what might the result need to look like.

[Anonymous]

Hi Sunny, I will try to load more rows, it will take me a little time as there is some info I need to remove from the data.  Thank you for taking a look at my problem.

My preferred output would be a sum of Cost/Sales/GP by Associate (User1, User2 in the test data) based on the [Invoice Date] and the date the Case Closes [ClosedDateAndTime].

I'd like to be able to filter on a month or a case# and say this Associate generated  'x' amount of dollars in Sales or GP based on this list of cases. The premise being that if a part is on a case and a sale involving that part happens shortly after the case closes, we can attribute that sale to the Associate. I may play around with the timeframes, but my initial thinking was an invoice date within 2 weeks of the case closing  [ClosedDateAndTime]..

I think I have a couple of issues, but maybe I'm overthinking at this point. Right now I essentially have two fact tables, [Fact] and [Sales Data]. Only one of these is connected to my master calendar.

My key to join those two tables is currently [SKUNumber], but I'm not sure that is relevant since I'm trying to match [SKUNumber] from the [Sales Data] against any matches from the part's fields (Part1, Part2, Part3, etc. ).

I'll come back as soon as I can with more data, hopefully that will help.

DJ 

[sunny_talwar]

Hey Darren -

Description you have provided helps, but make sure to provide with a numerical output you would want to see based on the new data you provide. Knowing the output, it will be much easier to decipher your problem.

[Anonymous]

I'm probably only addressing part of what you want to do.

But I think your first step is to split out the part numbers/skus into a separate table.

Your fact tables stays at 1 row per associate.

Your sales table stays at 1 row per part/sku.

And then you create a new table that links the 2 based on sku.

Something like

SKULinkTable:

load

Associate,

subfield(SKUNumberList,',') as SKUNumber

Resident Facts;

And get rid of the SKUNumber field on your Fact table.

[Anonymous]

Sunny,

Please see attached for the updated QVW with more data loaded, the data has 150 cases and 99 Invoices.

The expected output from the data should be:

User	Extended Sales $
User1:	192.09
User2:	44.70
User3:	2107.63

This is the sum of all of the parts sales  that were on a case and were invoiced within two weeks of the case closing [ClosedDateAndTime].

There are several parts that are on the cases and have associated sales, but were either sold prior to the case closing or outside the two week window, these have been excluded from the results. 

Please let me know if you need any further information. Thanks again for taking the time.

Best Regards,
Darren

[sunny_talwar]

Not sure how you got 192.09 for User1, but other two are matching up

Sum(If([Invoice Date] >= Floor(ClosedDateAndTime) and [Invoice Date] <= Floor(ClosedDateAndTime) + 14, [Extended Sales Price]))

[Anonymous]

Hi Sunny,

The reason that User1 does not match is because I am searching against all of the possible Parts on each case. This is the heart of the issue. Each case could have up to 10 part numbers. In the solution that you gave, you are only searching SKUNumber against SKUNumber between the two tables [Fact & SalesData]. It may be best to remove that relationship.

In my expected result, User1 worked on part 3099291 (Part4 on the case), the case closed on 1/24 and there was a sale on this part on 1/25 for $111.75. That's the difference in our results.

I need to to search all 10 fields (Part1, Part2, Part3...Part10) from the [Fact] table, for matching part numbers in the [SKUNumber] field from the [SalesData] table.

[sunny_talwar]

So based on the example you just gave for 3099291... I tried to check it in your app. It is not associated with Part 4 or any other part when I select 111.75. I am not sure how would your app know that it is related to 30999291 and not any other part?

[Anonymous]

Yes, you are correct, that's part of the problem that I'm having. I do not have the parts fields associated, but I'm not sure the best way to do that with 10 separate fields without creating a whole bunch of synthetic keys.

Sorry for my ignorance, the developer side is not my strong suit.

[sunny_talwar · Accepted solution]

This will increase the number of rows, but will give you what you want. Change in Facts table script

[Facts]:

LOAD CreatedDate as Date,

    CreatedTime,

    _cCreatedUserName as Associate,

    _cCaseNumber,

    CaseType,

    InquiryType,

    PricingSubInquiryType,

    PricingReasonCode,

    MarketType,

//    SKUNumber,

    SKUNumber_List,

    SubField(SKUNumber_List, ';') as SKUNumber,

    .....

FROM ....

No change on the front end

If you are concerned about Fact table getting huge, then you can try another alternative where you use SubField(SKUNumber_List, ';') as SKUNumber, in a link table which will keep your fact table small..... adding v2 with that option....

Data model for v1 remains the same, but the number of rows go up for the Facts Table

Data model for v2 changes with a new LinkTable, but the number of rows stay the same for Facts table