Solved: Re: Need help matching multiple fields over an int... - Qlik Community

Anonymous · ‎2017-07-06

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 · ‎2017-07-10

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

View solution in original post

Anil_Babu_Samineni · ‎2017-07-06

If possible, please explain with data set?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-07-06

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?

Anil_Babu_Samineni · ‎2017-07-06

Yes and that too expected result

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-07-06

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.

Anil_Babu_Samineni · ‎2017-07-06

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-07-07

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.

Anil_Babu_Samineni · ‎2017-07-07

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.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-07-07

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.

Anil_Babu_Samineni · ‎2017-07-07

+ stalwar1‌ mrkachhiaimp‌

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Need help matching multiple fields over an interval of time