Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Creating Dummy Entries or Null Counts in Load Script

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

1 Solution

Accepted Solutions
Not applicable

Re: Creating Dummy Entries or Null Counts in Load Script

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


8 Replies
Not applicable

Re: Creating Dummy Entries or Null Counts in Load Script

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


Re: Creating Dummy Entries or Null Counts in Load Script

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

Not applicable

Re: Creating Dummy Entries or Null Counts in Load Script

good.

MVP
MVP

Re: Creating Dummy Entries or Null Counts in Load Script

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;

Not applicable

Re: Creating Dummy Entries or Null Counts in Load Script

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

Not applicable

Re: Creating Dummy Entries or Null Counts in Load Script

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

Not applicable

Re: Creating Dummy Entries or Null Counts in Load Script

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

Re: Creating Dummy Entries or Null Counts in Load Script

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

Community Browser