Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
aniketsr
Creator
Creator

Help in inserting Missing records

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..

1 Solution

Accepted Solutions
Anonymous
Not applicable

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;

View solution in original post

5 Replies
Anonymous
Not applicable

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:

Capturar.PNG

aniketsr
Creator
Creator
Author

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
Not applicable

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
Creator
Creator
Author

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
Not applicable

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