topic Re: Residential Load Issue in QlikView

Residential Load Issue

Tue, 21 Oct 2014 02:45:23 GMT

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?

Re: Residential Load Issue

jyothish8807 — Tue, 21 Oct 2014 03:11:36 GMT

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

Re: Residential Load Issue

Tue, 21 Oct 2014 03:16:45 GMT

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

Thanks for the suggestion though!

Re: Residential Load Issue

jagan — Tue, 21 Oct 2014 03:25:48 GMT

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.

Re: Residential Load Issue

jyothish8807 — Tue, 21 Oct 2014 03:27:24 GMT

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

Re: Residential Load Issue

jagan — Tue, 21 Oct 2014 03:31:05 GMT

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.