Residential Load Issue

[#1]

I was wondering how I can reference columns from other tables within a residential load.

In the code below, i can’t reference NotifType, Task Code, System or BU because they’re not a part of the DMS table.

FunctLocTmp2:
Load FunctLoc,
If ([Document Type] = 'PMS Checklist',0,1) as [Not Submitted]
Resident DMS
where [NotifType]='MM' and [Task Code]='OSR' and (System<>'DYNACAD DIGITAL IMAGING WS' or System<>'HEMATEK' or System<>'PFA 100' or System<>'PFA 200' or System<>'BFT II') and BU<>'SY'
;

What is the best way around this?

[jyothish8807]

Hi Matt,

I guess try to join the DMS table with Other table which contain the reference fields (NotifType, Task Code, System or BU) say  Table1. Then try to do resident Table1.

Hope it may work.

Regards

KC

[#3]

The fields are scattered throughout my data model. I don't want to resort to that.

Thanks for the suggestion though!

[jagan]

Hi Matt,

You need to join tables and then you need to filter using where clause.  Try like this

Data:

LOAD

*

FROM TableName1;

INNER JOIN (Data)

LOAD

*

FROM TableName2

where [NotifType]='MM' and [Task Code]='OSR' and (System<>'DYNACAD DIGITAL IMAGING WS' orSystem<>'HEMATEK' or System<>'PFA 100' or System<>'PFA 200' or System<>'BFT II') and BU<>'SY';

Qlikview works like SQL even in SQL it is not possible, we join both the tables and then use where to filter the records.

Hope this helps you.

Regards,

Jagan.

[jyothish8807]

Hi Matt,

Then why dont you try to do a set analysis in the front end instead of a where clause in the backend.

In your chart you expression like:

Sum({<[NotifType]={'MM'}, [Task Code]={'OSR'} >}Yourfield)

Hope it may do the trick.

Regards

KC

[jagan]

Hi Matt,

Without join it is not possible in script, if you don't want to join the tables, then you can handle this in front end using Set Analysis like below

Sum({<[NotifType]={'MM'}, [Task Code]={'OSR'}, System-={'DYNACAD DIGITAL IMAGING WS', 'HEMATEK', 'PFA 100', 'PFA 200', 'BFT II'}, BU -={'SY'} >} MeasureName)

Note : Replace MeasureName with you actual field name you want to aggregate.

The above expression will be shown as syntax error but it works.

Hope this helps you.

Regards,

Jagan.