cancel
Showing results for 
Search instead for 
Did you mean: 

Customer categorisation query

Former Member
0 Kudos

Hi

Putting it out to the helpful experts again.

Trying to categorise customers by certain purchase combinations. Difficulty being that i'm having to rely on item description to ensure all data is captured and so am using the "contains" condition.

Categories i'm trying to cover are as follows,

  • Group 1: Product type A/ Product type B/ Product type C, Product Type D), Product type E), Product Type F
  • Group 2: Product type A/ Product type B/ Product type C, Product Type D), Product type E),
  • Group 3: Product type A/ Product type B/ Product type C, Product Type D),
  • Group 4: Product type A/ Product type B/ Product type C
  • Group 5: Product type A/ Product type B/

EG Group one contains only active BPs that purchase A&B&C&D&E&F

Group two contains only active BPs that purchase A&B&C&D&E

and so on.

Initial query is here but am running into issues going beyond the one product type.

SELECT count(distinct T0.[CardCode]) [No Of Customers]

FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T1.[Dscription] Like '%%[%0]%%' and cast(T1.DocDate as date) >= dateadd(dd, -180, cast(getdate() as date))

Many thanks in advance for any advice!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

HI

I didn't understand what you need

can you explain what you need in your query?

shachar

Former Member
0 Kudos

Sorry, a little convoluted.

I need to be able to see customers that have purchased items containing a description (eg cards). I can do this for one product selection but not multiple. I'd like to be able to select up to 6.

An example would be

All customers that have purchased this combination - CARDS and PAPER and RIBBON within the last 180 days.

Unfortunately, we have hundreds of products that are grouped differently and would like to include all products that contain the word card (for instance), hence the Like '%%[%0]%%'

Looking at that even i think it's crazy, Any help would be great though!

Former Member
0 Kudos

HI

you can try to display 6 text fields to the customer to define the words you wont to find inside the description

Try this query :

SELECT count(distinct T0.[CardCode]) [No Of Customers]

FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T1.[Dscription] Like '%%[%0]%%' or T1.[Dscription] Like '%%[%1]%%'
or T1.[Dscription] Like '%%[%2]%%' or T1.[Dscription] Like '%%[%3]%%'
or T1.[Dscription] Like '%%[%4]%%' or T1.[Dscription] Like '%%[%5]%%'

and cast(T1.DocDate as date) >= dateadd(dd, -180, cast(getdate() as date))

shachar

Former Member
0 Kudos

Thanks - the input fields upon executing the query are exactly what i want, but i think the issue there is that i'm not seeing a combination.

This query gives me anyone that's purchased any of these things, and i need to see only those that have purchased a combination of the item descriptions.

Former Member
0 Kudos

HI

You should try to put and between the conditions

SELECT count(distinct T0.[CardCode]) [No Of Customers]

FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T1.[Dscription] Like '%%[%0]%%' and T1.[Dscription] Like '%%[%1]%%'
and T1.[Dscription] Like '%%[%2]%%' and T1.[Dscription] Like '%%[%3]%%'
and T1.[Dscription] Like '%%[%4]%%' and T1.[Dscription] Like '%%[%5]%%'

and cast(T1.DocDate as date) >= dateadd(dd, -180, cast(getdate() as date))

shachar

Former Member
0 Kudos

Unfortunately that's not working for me - no data found. If i check individual customers i can see they should be included.

Former Member
0 Kudos

Hi

it depend on how many strings you want to find if you need all 6 than you need to fill all of them if you can have only 3 strings you can shorten your query

shachar

Former Member
0 Kudos

No, still not getting there i'm afraid. Thanks for trying

frank_wang6
Active Contributor
0 Kudos

Hi, Man,

I think u are going to the wrong direction.

It is better to do like this.

Create some item groups, or u can use item attributes (up to 64).

And then use DWT to categorize item into those group.

And then u can start to write your query.

Former Member
0 Kudos

Hi Frank

I've got these items categorised using the attributes now, but am confused as to where to go from here. Any tips?

Cheers.

frank_wang6
Active Contributor
0 Kudos

Once you have those in item master attribute, u can use those values in your query.

You don't need to contains in ur query any more. just use the new attribute values.

Frank

Former Member
0 Kudos

Sure, thanks.

This still doesn't solve my issue of being able to see customers that are purchasing a combination of items.

Example - i want to see only those customers that purchase items with attributes 1, 2 AND 3. I don't want to see those outside that combination. I'm looking at INV1 table - should i be looking elsewhere? Very frustrating!

Thanks again!

frank_wang6
Active Contributor
0 Kudos

u can query ur OINV and INV1 and use the item code there and sub query your OITM to get the category, since u r using attribute there, u can always add 6 columns for ur A-F category, and give 0 for item not in group, and 1 for in group.

so now, u have a result set some like

cardcode, itemcode, GroupA, GroupB, GroupC, GroupD, GroupE, GroupF

xxxxxx       abcd        1             1            1             1             1           1

with above set, u can sum on GroupA, GroupB, GroupC, etc

and for ur customer group 1, u just need to make sure those SUM of GroupA, GroupB, etc are not 0.

This is not hard. If u want, u can send me teamview.

Frank

Former Member
0 Kudos

Hi Frank

Been a few days where i walked away from it.

Bit of background, i'm just a B1 user, not an expert and have traditionally created very simple queries using query generator, so this is all a bit over my head - really keen to expand the knowledge though!

I can see what you're getting at with the above but to get there using my limited knowledge is proving a bit much! Appreciate your time and if you're able to help out with what that query might look like from start to finish that would be great - although understand if that's too much of an ask!

Thanks

Lisa

frank_wang6
Active Contributor
0 Kudos

SELECT T.CardCode, T.TypeA * 32 + T.TypeB * 16
+ T.TypeC * 8 + T.TypeD * 4 + T.TypeE * 2 + T.TypeF * 1 GROUP_NUM

FROM

(

SELECT T0.CardCode,
MAX(CASE WHEN T2.QryGroup1 = 'N' THEN 0 ELSE 1 END) TypeA,
MAX(CASE WHEN T2.QryGroup2 = 'N' THEN 0 ELSE 1 END) TypeB,
MAX(CASE WHEN T2.QryGroup3 = 'N' THEN 0 ELSE 1 END) TypeC,
MAX(CASE WHEN T2.QryGroup4 = 'N' THEN 0 ELSE 1 END) TypeD,
MAX(CASE WHEN T2.QryGroup5 = 'N' THEN 0 ELSE 1 END) TypeE,
MAX(CASE WHEN T2.QryGroup6 = 'N' THEN 0 ELSE 1 END) TypeF


FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE T0.DocDate BETWEEN GETDATE()- 180 AND GETDATE()
GROUP BY T0.CardCode) T

Former Member
0 Kudos

Thanks Frank, really appreciate your time - this is what i'm getting as a result.

What i'd like to see is something like this but narrowed down to one line per customer. I don't even know if it's possible.

frank_wang6
Active Contributor
0 Kudos

ha, then remove the outer part

SELECT T0.CardCode,
MAX(CASE WHEN T2.QryGroup1 = 'N' THEN 0 ELSE 1 END) TypeA,
MAX(CASE WHEN T2.QryGroup2 = 'N' THEN 0 ELSE 1 END) TypeB,
MAX(CASE WHEN T2.QryGroup3 = 'N' THEN 0 ELSE 1 END) TypeC,
MAX(CASE WHEN T2.QryGroup4 = 'N' THEN 0 ELSE 1 END) TypeD,
MAX(CASE WHEN T2.QryGroup5 = 'N' THEN 0 ELSE 1 END) TypeE,
MAX(CASE WHEN T2.QryGroup6 = 'N' THEN 0 ELSE 1 END) TypeF


FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE T0.DocDate BETWEEN GETDATE()- 180 AND GETDATE()
GROUP BY T0.CardCode

Former Member
0 Kudos

Frank, i could kiss you.

Thank you so much! Absolute life saver.

frank_wang6
Active Contributor
0 Kudos

glad to be helpful.

Frank

Answers (0)