Creating Dummy Entries or Null Counts in Load Script

[#1]

Hi There QV experts.

I have a master table where my full data set resides;

MasterTable:

IDNOS

Field1

Field2

(100 records)

Left Join

     IDNOS

     Field3

(20 records)

Left Join

     IDNOS

     Field4

(30 records)

So my resulting dataset is;

IDNOS

Field1

Field2

Field3

Field4

(100 records)

My problem

I want the Field3 in my output to display the text 'NotAvailable' where the IDNOS exists in the MasterTable but not in the data I am using to left join from.

When done I want to be able to count contents of Field3 so that the counts equal 100 not 20 (i.e. so 80 of these will be the count of  'NotAvailable').

Ditto for Field4.

Thanks!

JW

[#2 · Accepted solution]

You could use ApplyMap instead of Left Join……hope this helps:

Map_1:

Mapping Load

     IDNOS

     Field3

from……..

Map_2:

Mapping load

     IDNOS

     Field4

from …...

MasterTable:

Load

IDNOS

Field1

Field2

ApplyMap('Map_1',IDNOS,'NotAvailable') as Field3,

ApplyMap('Map_2',IDNOS,'NotAvailable') as Field3

from…..

[sunny_talwar]

You can do something like this:

TempTable:

LOAD IDNOS,

  Field1,

  Field2

FROM source1

Left Join

LOAD IDNOS,

  Field3

FROM source2

Left Join

LOAD IDNOS,

  Field4

FROM source 3

MASTERTABLE:

LOAD IDNOS,

          Field1,

          Field2,

          If(Len(Trim(Field3)) = 0, 'Not Available', Field3) as Field3,

          Field4

Resident TempTable;

DROP Table TempTable;

HTH

Best,

S

[#4]

good.

[maxgro]

just add this at the end of your script

RENAME Field Field3 to TmpField3;

Left join (MasterTable)         

load IDNOS, if(IsNull(TmpField3), 'NotAvailable', TmpField3) as Field3

resident MasterTable;

DROP Field TmpField3;

[#6]

Hi sana.ismail

Thank you for your fast and accurate response.

This worked brilliantly, as did Massimo's suggestion too.  Shame I cant mark both suggestions as correct.

This has saved me SO much time.

Jason

[#7]

sana.ismail Hi Massimo

Thank you for your fast and accurate response.

This worked brilliantly, as did sana.ismail's suggestion too.  Shame I can't mark both suggestions as correct.

This has saved me SO much time. 

Jason

[#8]

Hi S

Thank you for your response.  I have tried to apply the suggestion but on reload I get "no data to display".

Maybe I have the syntax wrong but I can't see where I may have copied incorrectly.

Thanks again,

Jason

[sunny_talwar]

Did you change the path of the file from where the data is coming from? (Text in red below?)

TempTable:

LOAD IDNOS,

  Field1,

  Field2

FROM source1

Left Join

LOAD IDNOS,

  Field3

FROM source2

Left Join

LOAD IDNOS,

  Field4

FROM source 3

MASTERTABLE:

LOAD IDNOS,

          Field1,

          Field2,

          If(Len(Trim(Field3)) = 0, 'Not Available', Field3) as Field3,

          Field4

Resident TempTable;

DROP Table TempTable;

If you did, will you be able to share your excel file for me to check?

Best,

S