topic Re: Need help matching multiple fields over an interval of time in QlikView

Need help matching multiple fields over an interval of time

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Need help matching multiple fields over an interval of time

Anil_Babu_Samineni — Thu, 06 Jul 2017 18:06:16 GMT

If possible, please explain with data set?

Re: Need help matching multiple fields over an interval of time

Anonymous — Thu, 06 Jul 2017 18:45:35 GMT

Thanks Anil, I will see if I can mock up an example. I can't share the exact data as it is proprietary information.

Are you just looking for the tables with headers and a few rows of data, the script, or a qvw?

Re: Need help matching multiple fields over an interval of time

Anil_Babu_Samineni — Thu, 06 Jul 2017 18:47:59 GMT

Yes and that too expected result

Re: Need help matching multiple fields over an interval of time

Anonymous — Thu, 06 Jul 2017 20:18:08 GMT

Hi Anil, I've done a partial reload with only a couple of records so that I can share the QVW (attached). You should be able to view the script and schema. I've put the field and Table names below in brackets [] when i reference them.

I'm trying to figure out how many dollars each [Associate] could be generating in sales based on their work. In the [Fact] table I have the total time for each case [CaseAge] along with the timestamp for when the case opens [Date] and closes [ClosedDateAndTime]. The [Associate] table also has all of the parts (up to 10) that were on the case.

The sales data are in the table [SalesData]. In this table I have the parts that were invoiced [SKUNumber] along with the [Invoice Date].

So... I would like to Sum [Extended Gross Profit] based on the invoiced data [SalesData] where the parts [SKUNumber] match on a case and the [Invoice Date] is within two weeks. That would be tricky enough, but keep in mind that there are up to 10 parts on each case. Those fields are a subfield of the ';' delimited field [SKUNumber_List]: [Part1], [Part2]...[Part10].

This will give us a ballpark figure for how many Dollars in GP and Sales each Associate contributes.

Let me know what you think.... I'm sorry my explanation is not clearer, please let me know if you need more info. Thank you again for your help.

Re: Need help matching multiple fields over an interval of time

Anil_Babu_Samineni — Fri, 07 Jul 2017 02:45:11 GMT

So... I would like to Sum [Extended Gross Profit] based on the invoiced data [SalesData] where the parts [SKUNumber] match on a case and the [Invoice Date] is within two weeks. That would be tricky enough, but keep in mind that there are up to 10 parts on each case. Those fields are a subfield of the ';' delimited field [SKUNumber_List]: [Part1], [Part2]...[Part10].

From this part, You could try something like below

If(Match([SKUNumber], 'Part1', 'Part2', .....) = [Invoice Date], Sum({<Week = {">=$(=Max(Week)-2) <=$(=Max(Week))"}>} [Extended Gross Profit])

Re: Need help matching multiple fields over an interval of time

Anonymous — Fri, 07 Jul 2017 12:39:24 GMT

Hi Anil, If I understand correctly, the Match function the way you have written it is checking the Field [SKUNumber] for the Values 'Part1', 'Part2' etc from within the field. I'm looking for matching values between multiple fields in this case.

I'd like to search 10 fields (Part1 through Part10) for matching part numbers in the [SKUNumber] field. This is like doing 10 separate vLookups in Excel and adding the Gross Profit from each row where a match is found.

Thanks again for your help.

Re: Need help matching multiple fields over an interval of time

Anil_Babu_Samineni — Fri, 07 Jul 2017 14:10:10 GMT

If you have excel formula, please share that. Then we came to know how formula exists and used. Meanwhile, we will help you the formula in Qlikview.

Re: Need help matching multiple fields over an interval of time

Anonymous — Fri, 07 Jul 2017 14:15:53 GMT

I do not have an excel formula. I was just trying to use excel as an analogy for how the problem could be solved.

Basically I have up to 10 parts on any single case. Anytime one of those parts matches my sales data I would like to sum Gross Profit.

I'm just not sure how to search multiple fields in this manner. Maybe a really long nested if.

Re: Need help matching multiple fields over an interval of time

Anil_Babu_Samineni — Fri, 07 Jul 2017 14:47:58 GMT

+ stalwar1‌ mrkachhiaimp‌

Re: Need help matching multiple fields over an interval of time

sunny_talwar — Fri, 07 Jul 2017 18:53:21 GMT

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.

Re: Need help matching multiple fields over an interval of time

Anonymous — Fri, 07 Jul 2017 19:11:54 GMT

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.

Re: Need help matching multiple fields over an interval of time

sunny_talwar — Fri, 07 Jul 2017 19:14:36 GMT

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.

Re: Need help matching multiple fields over an interval of time

Anonymous — Fri, 07 Jul 2017 19:17:04 GMT

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.

Re: Need help matching multiple fields over an interval of time

Anonymous — Sat, 08 Jul 2017 19:46:42 GMT

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

Re: Need help matching multiple fields over an interval of time

sunny_talwar — Sun, 09 Jul 2017 04:47:39 GMT

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]))

Re: Need help matching multiple fields over an interval of time

Anonymous — Mon, 10 Jul 2017 12:49:31 GMT

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.

Re: Need help matching multiple fields over an interval of time

sunny_talwar — Mon, 10 Jul 2017 12:56:53 GMT

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?

Re: Need help matching multiple fields over an interval of time

Anonymous — Mon, 10 Jul 2017 13:51:06 GMT

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.

Re: Need help matching multiple fields over an interval of time

sunny_talwar — Mon, 10 Jul 2017 15:23:48 GMT

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