Ho to get a particular set of values from another set of values

[Greeshma_Sampath1]

Hi Team,

I want to filter out some records based on the below conditions.

example:

Input records:

customer id	customer type	Date
1	Full time	01-01-2016
1	Full time	01-01-2018
1	Full time	03-01-2020
1	Part Time	04-01-2021
1	Part Time	01-01-2015
2	Full time	06-01-2016
2	Part Time	01-01-2019
3	Full time	08-01-2016
3	Part Time	12-01-2016
3	Part Time	10-01-2017
3	Part Time	18-01-2021
4	Full time	12-01-2016

Output records expected:

On first run:

customer id	customer type	Date
1	Full time	01-01-2016
1	Full time	01-01-2018
1	Full time	03-01-2020

On second run:

customer id	customer type	Date
1	Part Time	04-01-2021
1	Part Time	01-01-2015

On third run:

customer id	customer type	Date
2	Full time	06-01-2016

Etc

So basically i want a filtering technique which will filter out all the records for that particular customer with same customer type.

I tried with deduplication but only the latest one or any random one is coming.

Could someone please help me in this?

@Manoj_Kumar_  @Jyoti_Yadav 

Thanks &regards

Greeshma

[David_Loyd]

Have you tried a split on customer type then a dedup on customer id? in the dedup you can use the advanced tab to keep the oldest or newest based on the date.

[Greeshma_Sampath1]

Hi @David_Loyd ,

Thanks for the response.

Split on a particular customer id will not help is what i thought as the customer ids can be hundreds or thousands so a generic way to filter is what iam looking for.

[David_Loyd]

Split on customer TYPE (not customer ID) is what I was recommending. Based on your data set seems you only want "Full Time"

[Greeshma_Sampath1]

So i tried to do split on customer type as "full time" and deduplicate on the customer id and the result was only  record. 

Is there a way to bring all those 3 records as output?

[David_Loyd]

Its not clear what you want. Initially you said "filter out all the records", then you are saying you want to keep the ones that get filtered out.

Do you want to aggregate the values? Or do you want to market to one person? What is the result you want?

If you want to aggregate check out step 1 here:https://experienceleague.adobe.com/docs/campaign-classic/using/automating-with-workflows/use-cases/d... 

instead of Max(), you can use Count(). That will give you that total count per person, while keeping all the records.

[Greeshma_Sampath1]

As shown in the example above.. I want to filter out the set of values based on customer id and customer type.

As you can see in the input values we have around 5 customers with id 1 and amoung them only 3 are having customer type "full time" . So the output or the result i need is these 3 records at a time. After this again the flow should run and pick the customer id 1 with customer type "part time" and give out those 2 records and so on with other customer ids. 

I am hoping the issue is clear now.If not feel free to ask.

Thanks & Regards,

Greeshma 

[Jyoti_Yadav · Accepted solution]

Hi @Greeshma_Sampath1 ,

You can use below logic.

1) Use query activity to fetch all the customers. Here your output will have all the records from customer table. Define one variable, : vars.customerId = "1";

2) Add test activity to check if number of records greater than zero.

3) Use 'split' activity. Split all your customers based on different types of customer type. Also customer Id equals to $(vars/@customerId);

Also, add the complimentary branch. This will have all the records other than customer Id 1.

4) Continue with complimentary branch. Use Javascript to increment the vars.customerId variable by 1.

5) Use wait activity. Give wait time of 5 sec. Connect the output branch to Step 2, 'test' activity.

Thanks,

Jyoti

[Krishnanunni]

Hi @Greeshma_Sampath1 ,

If I understand the requirement correctly, then what you would like to have is a looped execution of query with different results at each execution. I think the following structure of workflow might satisfy that requirement.

I have created a file based on your data and loaded using the data loading.
Code inside definition:

var query = xtk.queryDef.create(
  <queryDef schema={vars.targetSchema} operation="select">
    <select>
      <node expr="customer_id"/>
      <node expr="customer_type"/>
    </select>
  </queryDef>
)

var res = query.ExecuteQuery()

var keyValPair = {};
for each (var row in res)  {
  logInfo(row.customer_id);
  logInfo(row.customer_type);
  keyValPair[row.customer_id+row.customer_type] = [row.customer_id, row.customer_type];
}

keys=[];
vals=[];
len = 0;
for (var item in keyValPair) {
     keys.push(item);
     vals.push(keyValPair[item]);
     len++;
}
//logInfo(keys)
instance.vars.combinations = vals;



//loop variable
instance.vars.loop = 0;
instance.vars.loopLength= len;

You may skip data loading as you would have the original data in some schema. So replace the {var.targetSchema} with schema name like "nms:recipient". You might need to add '@' before column names when using schema. 

Code inside loop definition:

instance.vars.cust_id = instance.vars.combinations[instance.vars.loop][0];
instance.vars.cust_type = instance.vars.combinations[instance.vars.loop][1];

In query activity, set the value expression as in below ss

Code in JavaScript code 3

instance.vars.loop++;

Test activity

[Sukrity_Wadhwa]

Hi @Greeshma_Sampath1,

Were you able to resolve this query with the help of any of the given solutions or do you still need more help here? Do let us know.
Thanks!