Help in inserting Missing records

[aniketsr]

Hi All,

I have attached a excel sheet with a record set along with the expected output .

The rows highlighted in blue is the expected output .

Thanks..

[Anonymous]

Hi, aniketsr!

Try the script below:

Data:

LOAD *,

          Type&Dimension As Key

FROM [Sample Data for Dummy records.xlsx]

(ooxml, embedded labels, table is Sheet2);

TmpTable:

LOAD Distinct Type

Resident Data;

Join

LOAD 1 As ID,

           'PROD' As DIMType,

           Dimension,

           0 As Value

Resident Data;

Outer Join(Data)

Load *

Resident TmpTable Where Not Exists(Key, Type&Dimension);

Drop Field Key;

Drop Table TmpTable;

Result:

[aniketsr]

Hi,

this is just a sample data ,

here in your code you have hard coded 1 as ID to bring the 0 Values , but as per the actual data it should it has to be dynamic.

[Anonymous · Accepted solution]

Hello again!

Just adapt the code:

Data:

LOAD *,

           Type&Dimension&ID&DIMType As Key

FROM [Sample Data for Dummy records.xlsx]

(ooxml, embedded labels, table is Sheet2);

TmpTable:

LOAD Distinct Type,

           ID,

           DIMType

Resident Data;

Join

LOAD Dimension,

           0 As Value

Resident Data;

Outer Join(Data)

Load *

Resident TmpTable Where Not Exists(Key, Type&Dimension&ID&DIMType);

Drop Field Key;

Drop Table TmpTable;

[aniketsr]

I don't think the below join would work:

TmpTable:

LOAD Distinct Type,

           ID,

           DIMType

Resident Data;

Join

LOAD Dimension,

           0 As Value

Resident Data;

[Anonymous]

Have you tried to run the code? What are the output and/or possible errors you are getting?