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

[khigaurav999]

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

[vinieme12]

use Applymap()

Don't join - use Applymap instead

[vinieme12 · Accepted solution]

a video guide

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

[sdmech81]

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.

[sdmech81]

Something like this

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

Sachin

[khigaurav999]

Thanks Vineeth! ApplyMap function really solves the problem!