Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Sept. 16, Qlik Product Portfolio Strategy and Roadmap for Data Integration 11 AM ET REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
khigaurav999
Creator
Creator

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

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

1 Solution
5 Replies
vinieme12
Champion II
Champion II

sdmech81
Specialist
Specialist

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
Specialist
Specialist

Something like this

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

Sachin

khigaurav999
Creator
Creator
Author

Thanks Vineeth! ApplyMap function really solves the problem!