topic Re: Creating Dummy Entries or Null Counts in Load Script in QlikView

Creating Dummy Entries or Null Counts in Load Script

Fri, 20 Mar 2015 12:51:26 GMT

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!

Re: Creating Dummy Entries or Null Counts in Load Script

Fri, 20 Mar 2015 18:35:01 GMT

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

sunny_talwar — Fri, 20 Mar 2015 18:48:16 GMT

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,

Re: Creating Dummy Entries or Null Counts in Load Script

Fri, 20 Mar 2015 19:11:39 GMT

good.

Re: Creating Dummy Entries or Null Counts in Load Script

maxgro — Sat, 21 Mar 2015 11:13:33 GMT

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;

Re: Creating Dummy Entries or Null Counts in Load Script

Mon, 23 Mar 2015 10:31:53 GMT

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

Re: Creating Dummy Entries or Null Counts in Load Script

Mon, 23 Mar 2015 10:33:21 GMT

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

Re: Creating Dummy Entries or Null Counts in Load Script

Mon, 23 Mar 2015 10:39:33 GMT

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

sunny_talwar — Mon, 23 Mar 2015 10:43:50 GMT

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,