topic Re: How reference and use field from a previously loaded table Or Resident Load in QlikView

How reference and use field from a previously loaded table Or Resident Load

khigaurav999 — Wed, 25 Nov 2020 16:16:04 GMT

Dear Members and Friends,

I am new to QlikView. I have following load script:

[CR Type CR Group Mapping]:

LOAD CR_TYPE,

CR_GROUP

FROM

(ooxml, embedded labels, table is [CR Type and CR Group Mapping]);

[Product DA Suite Mapping]:

LOAD PRODUCT_NAME_1,

PRODUCT_SUITE

FROM

(ooxml, embedded labels, table is [Product and D&A Suite Mapping]);

[CR QBR Data]:

LOAD CR_NUMBER,

ACCOUNT_NAME,

IATA_CODE,

CR_TYPE,

CR_STATUS,

PRODUCT_NAME,

SETTLEMENT_TYPE,

ESTIMATE_TYPE,

ESTIMATE_HRS,

BILLABLE_HRS,

RATE,

[ESTIMATE_HRS]-[SABRE_FUNDED_HRS] as CAL_BILLABLE_HRS,

([ESTIMATE_HRS]-[SABRE_FUNDED_HRS])-[BILLABLE_HRS] as CAL_DIFF_TO_ACTUAL_BILLABLE_HRS,

IF(MixMatch(ESTIMATE_TYPE,'Contracted amount')>0,RATE,'0') as Contracted_Price,

IF(MixMatch(ESTIMATE_TYPE,'Time & Materials')>0,RATE*BILLABLE_HRS,'0') as TM_Billable_Price,

If ([PRODUCT_NAME] = [PRODUCT_NAME_1], 'Data & Analytics - AS', PRODUCT_LINE) as PRODUCT_LINE Resident [Product DA Suite Mapping] Group By [ESTIMATE_TYPE]

FROM

(qvd);

The aim is to check if [CR QBR Data]. PRODUCT_NAME and [Product DA Suite Mapping].PRODUCT_NAME_1 are equal and update PRODUCT_LINE field in [CR QBR Data] with value 'Data & Analytics - AS' and load [CR QBR Data] with transformed PRODUCT_LINE. I did research and heard about Resident load and thought off applying it but not getting success.

I initially did it without Group By but received an error saying Aggregation expressions required by GROUP BY clause then I just Grouped it by ESTIMATE_TYPE field which is not really part of the requirement but continue to received same error.

Please let me know what is wrong here or if there is any other better approach to achieve same.

Thanks,

Gaurav

Re: How reference and use field from a previously loaded table Or Resident Load

vinieme12 — Mon, 30 Jan 2017 10:41:49 GMT

use Applymap()

Don't join - use Applymap instead

Re: How reference and use field from a previously loaded table Or Resident Load

vinieme12 — Mon, 30 Jan 2017 10:45:20 GMT

a video guide

Using ApplyMap() Function with Mapping Load in QlikView - QlikView Blog, QlikView Videos and Tips at QlikShare

Re: How reference and use field from a previously loaded table Or Resident Load

sdmech81 — Mon, 30 Jan 2017 11:05:28 GMT

Hi,

Do concatenate all the 3 tables and say you have built the Qlik Fact table. Assume u didn't alter any names.

In next use the resident load and while loading use IF(PRODUCT_NAME=PRODUCT_NAME_1,''Data & Analytics - AS'') condition to get the desired result. Similarly put other if conditions as well.

Hope this helps.

Sachin.

Re: How reference and use field from a previously loaded table Or Resident Load

sdmech81 — Mon, 30 Jan 2017 11:07:24 GMT

Something like this

IF(PRODUCT_NAME=PRODUCT_NAME_1,''Data & Analytics - AS'') as New_attribute _name

Sachin

Re: How reference and use field from a previously loaded table Or Resident Load

khigaurav999 — Mon, 30 Jan 2017 11:24:22 GMT

Thanks Vineeth! ApplyMap function really solves the problem!