LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Highest Unique Count by Group - Tallying through multiple files

    Employee
    Posted 03-03-2015 13:42

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: jpstory

    Hi,

    I need to tally through multiple files to find which file has the highest sum of 'Quantity' on records that meet with certain criteria

    I have worked out the filter to get records meeting the criteria, but how to I do that on 30 files?

    Also, I need to remove duplicate on the 'Model' field.

    Thirdly, all files are zipped, I have set up a "Dircotry List" that is mapped to a folder but How do I connect it to my filter above?

    File Content/Format: Region:String (US, UK, HK), Product: String (Cellphone, Tablet, PC), Quantity: Int ( >=0 ), Producer: String ( A, B, C, D)
    Region:String Product:String Quantity:int Producer:String Model: String
    US Cellphone 119 A A1G356G33
    UK PC 176 B A1G356G34
    HK Tablet 199 C A1G356G33
    UK PC 56 D A1G356G34
    US Cellphone 61 B A1G356G37
    UK Tablet 121 C A1G356G34
    US PC 102 A A1G356G39
    HK Tablet 136 D A1G356G34
    UK Cellphone 113 D A1G356G41
    US PC 123 C A1G356G34
    HK Cellphone 135 A A1G356G43
    US Tablet 114 B A1G356G33

    2. Criteria:
    If Producer = A or C and Quantity > 20
    If Producer = B or D and Quantity > 50


  • 2.  RE: Highest Unique Count by Group - Tallying through multiple files

    Employee
    Posted 03-24-2015 09:47

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ltolleson

    You can use the DirectoryList node to read in all 30 filenames. You will then use those records as input in the DelimitedFile node. You will set the FileNameExpr parameter on the DelimitedFile node to the field 'FileName'. This will use the field called FileName and read the records from all files into a single output. If you need the abiilty to identify the data from each file you can populate the FilenameOutputField parameter on the DelimitedFile node under the Output tab. This will create a new field in the data which contains the filename.

    Once you have all of the data read into a single output for all 30 files then you can Filter or Agg the data according to your rules.

    Hope this helps... Sorry about the late response.