How to create a table showing a field and excluded values of another field

[sudeepkm]

Hi I've three fields as File Name, Sheet number and Field Names.

I'm loading multiple fields from multiple tabs from multiple xls files.

Few of the files does not have all the necessary fields. So I've an inline load table that has all the fields used. I've a logic that runs and create a table by capturing the fields present in all the files. As given below when I select  a file name and sheet number I can see what all fields are missing listed as Excluded  fields.

I need help in showing them in a single table where I can show the file name, sheet number and only those fields which are missing or excluded from that file. Thanks in advance.

[hic]

I would add an additional table in the data model - a logical island - with just one field:

     Load distinct [Field Name] as [Missing Field] resident Data;

Then I would create a pivot table with [File Name] and [Sheet Number] as Dimensions and the following expressions:

     Concat(distinct [Field Name], ',') as 'Existing Fields'

     Concat(distinct if([Field Name]<>[Missing Field],[Missing Field]), ',') as 'Missing Fields'

HIC

[sudeepkm]

Hi Henric, thanks for your reply. I tried the solution you provided but for some reason it is showing all the fields under the missing field column. I've attached the entire application folder in zip format here.

Here is the code I've used:

Missing:

load * Inline [

MissingFields

Field1

Field2

Field3

Field4

];

let vDataFolder = '..\DataSources\';

Set ErrorMode=0;

// Check if file exists in the folder

if(FileSize('$(vDataFolder)*.xls')>0) then

    // Loop through each file under DataSource folder

    for each vFiles in filelist('$(vDataFolder)*.xls')

        // Loop through 3 sheets of each file

        for i = 1 to 3

            // Try to load table with all field names

            tabb:

            LOAD FieldsList, Field1, Field2, Field3, Field4

            FROM [$(vFiles)](biff, embedded labels, table is @$(i));

            // Check if field not found occurs

            if(ScriptError = 11) then

                // Load in transpose mode, the first field being the only field and other fields as values

                newtab:

                LOAD FileName() as fName,$(i) as SheetNumber, FieldsList

                FROM [$(vFiles)](biff, embedded labels, table is @$(i), filters(Transpose()));

            ENDIF   

        NEXT i;

    NEXT vFiles;

    drop Table tabb;

ELSE

EXIT Script;

ENDIF   

Thanks and Regards,

Sudeep

[hic · Accepted solution]

You're right. It doesn't work.

Then you will instead have to do it in the script. See attachment:

HIC

[sudeepkm]

Hi Henric,

Thanks a lot for your quick help. It worked. I changed the code a little bit. I'm only showing the missing fields.

Load distinct FieldsList as MissingFields Resident newtab; // this code was only loading fields those are present in a File where other fields are missing so it was not keeping all the fields.

1. I created an inline table with all possible fields:

Missing:

load * Inline [

MissingFields

Field1

Field2

Field3

Field4 ];

2. Then I use your logic like below:

PreMissingFields:

Noconcatenate Load MissingFields Resident Missing;

Join

Load distinct

    fName,

    SheetNumber

    Resident newtab;

Drop Table Missing;

MissingFields:

Noconcatenate Load *

    Resident PreMissingFields Where Not Exists(FieldKey, fName & '|' & SheetNumber & '|' & MissingFields);

Drop Table PreMissingFields; Drop Table newtab;

Thanks again for your help.

Regards,

Sudeep