topic Re: Help in inserting Missing records in QlikView

Help in inserting Missing records

aniketsr — Tue, 26 Jun 2018 19:05:39 GMT

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

Re: Help in inserting Missing records

Anonymous — Wed, 27 Jun 2018 02:23:13 GMT

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:

Re: Help in inserting Missing records

aniketsr — Wed, 27 Jun 2018 03:22:07 GMT

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.

Re: Help in inserting Missing records

Anonymous — Wed, 27 Jun 2018 03:42:19 GMT

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;

Re: Help in inserting Missing records

aniketsr — Wed, 27 Jun 2018 05:52:50 GMT

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;

Re: Help in inserting Missing records

Anonymous — Wed, 27 Jun 2018 13:01:18 GMT

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