Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
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?