Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm struggling, I don't see the answer, I'm hoping somebody can point me in the right direction.
I have a 3 level product category table in Excel. Half a dozen distinct first level values . A dozen distinct second level values for each distinct first level value. Two dozen distinct third level values for each distinct second level value. I load the Excel into a table.
During the load of 200k products I need to check the value of each products category values.
The first level is easy If(Exists(masterFirstLevelCat, FirstLevelCat), 'True', 'False') as validFirstLevelCat
Moving to the second level category I have to limit the range of the field masterFirstLevelCat by some how filtering masterFirstLevelCat. I have no idea how to do that 😟
Any ideas?
Thanks.
Try this
If(Exists({<FirstLevelCat = {'$(=GetFieldSelections(FirstLevelCat))'}, SecondLevelCat = {'$(=GetFieldSelections(SecondLevelCat))'}>} FirstLevelCat), 'Valid', 'Invalid')
G'day @smarties, I'm not sure I follow how your data is structured. It seems that you have a Product table with 200k rows and a Hierarchy table with roughly 100 rows. The hierarchy values appear to be embedded directly in the Product table and you are checking them against the Hierarchy table. If this is the case, I think you're going to need to use applymap() to bring the different levels or the hierarchy table into the main Product table. Then you can compare them directly similar to your level one test above.
@Chanty4u - I don't think your solution will work as the exists() function does not allow for set analysis (I checked the docs)
I hope this helps. Cheers, Barnaby.
@smarties To be honest it is not clear what you are trying to achieve. If you can put up scenario and expected output with some sample data examples it will useful
Thanks @barnabyd, @Kushal_Chawda, and @Chanty4u.
As @barnabyd says, set analysis is not supported in the scripting. If it was, @Chanty4u nails the answer. This is where my head went initially, and I struggled to move past it.
I began to prepare an example to share with you. As I was, the answer hit me. Combine the category fields in the master table of categories from Excel as they load, and also combine the product category fields as they load. Then do a compare of a combined category fields to mark those products with errors in their category fields.
An example with two levels of category below.
Thanks for taking the time to help. Appreciated.
ValidCategories:
Load
*,
validCat & '|' & validSubcat as validCats
Inline
[
validCat, validSubcat
AAAA, mmmm
AAAA, nnnn
AAAA, oooo
AAAA, pppp
BBBB, eeee
BBBB, ffff
CCCC, tttt
CCCC, uuuu
CCCC, vvvv
CCCC, wwww
];
ProductData:
Load
*,
If(Exists(validCats, Category & '|' & Subcategory), 'True', 'False') as goodCategory
Inline
[
PartNum, Category, Subcategory
1122, AAAA, nnnn
1123, AAAA, wwww
1124, BBBB, ffff
2245, AAAA, nnnn
2246, CCCC, uuuu
2247, CCCC, mmmm
2248, BBBB, vvuu
3046, CCCC, tttt
3047, AAC, nnnn
];
// PartNums 1123, 2247, 2248, and 3047 have an invalid category or subcategory.