Limiting the range of a field used in an Exists() statement.

[smarties]

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.

[Chanty4u]

Try this 

If(Exists({<FirstLevelCat = {'$(=GetFieldSelections(FirstLevelCat))'}, SecondLevelCat = {'$(=GetFieldSelections(SecondLevelCat))'}>} FirstLevelCat), 'Valid', 'Invalid')

[barnabyd]

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.

[Kushal_Chawda]

@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

[smarties]

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.