topic Re: Inline mapping load before binary load? in QlikView

Inline mapping load before binary load?

NewToQlik — Sat, 16 Nov 2024 05:13:47 GMT

Hi all,

In my data model qvf, it contains all the inline mapping load tables. However, in my application app, I realised that my applymap() script do not get loaded in my application app after I use binary load.

Is there any way to do this?

Much help is appreciated. Thank you so much.

Re: Inline mapping load before binary load?

lblumenfeld — Fri, 16 Nov 2018 06:25:23 GMT

Do you mean that you

1. Create the mapping tables in App 1.
2. Then you binary load App 1 into your app (App 2).
3. And then you try to do an applymap with the tables created in App 1?

If that it the case then that is not possible. Mapping tables are automatically dropped at the end of script execution, so the mapping tables you created in App 1 no longer exist.

If you'd like o use them as mapping tables then I suggest you create them as permanent tables in App 1, not mapping tables.

Then in your app, do a resident load into a new mapping table and drop the old table (from App 1).

For example, in the first app (App 1), create the table as a regular table (not mapping). Here's a simple table...

States:
Load * Inline [
Abbrev, StateName
AL, Alabama
NY, New York
TX, Texas
];

Then in your new app (App 2)

Binary 'App 1'; // You would put the real app name here.

State_Map:
Mapping Load
Abbrev,
StateName
Resident States;

Drop Table States; //We don't need it anymore, because we created a mapping table from it.

// then you can use State_Map in an ApplyMap.

I hope this helps.

Re: Inline mapping load before binary load?

Gysbert_Wassenaar — Fri, 16 Nov 2018 07:35:10 GMT

The binary load MUST be the FIRST command in the script. There's no way around that. But the binary load will only load the entire data model from another qvw or qvf file, not its script. So I don't understand why you have problems with applymap. That would mean you're doing additional processing of data in your script after the binary load. And if that's the case you can create the needed mapping tables directly after the binary load and then later in the script use the applymap function to use your mapping tables.

If this didn't answer your question please give some more information about what you're doing in your script and what result you are afger.

Re: Inline mapping load before binary load?

NewToQlik — Sat, 17 Nov 2018 11:34:31 GMT

Both proposed solution is the same, and I managed to get it working. Thanks for all your help!

EDIT: I found some problems in my data, I hope I am still able to get some help.

In App 1, I have loaded my tables normally, Eg. Table1, Table2.

In App 2, I binary loaded App 1, then created the mapping tables. However, I want to add a new column in Table1, that uses the applymap(). How do I achieve this?

Re: Inline mapping load before binary load?

NewToQlik — Sun, 18 Nov 2018 02:02:34 GMT

I tried loading Table1 in App1 and left joining it in App2 but it did not work. Any ideas?

Re: Inline mapping load before binary load?

vishsaggi — Sun, 18 Nov 2018 02:10:39 GMT

Can you share your script in your app 2 where you have this left join ? And when you left join your table name will be Table1 right? So where are you using this applymap() and where you want to add this new column. You might have to do a resident load to add a new column on table1. I am assuming you were doing like

1. Binary load your table1 into app2
2. Left Join(Table1)
Table2:
LOAD .....
FROM Table2source;

May be here you can add Mapping table and use your apply map on below script.

NoConcatenate
Finaltable:
LOAD *, Applymap() as yournewfield
Resident Table1;

Drop Table Table1;

Re: Inline mapping load before binary load?

NewToQlik — Sun, 18 Nov 2018 04:50:22 GMT

Hi, this is my script in App 1.

CRSE_GRADE_INPUT_MAP:
LOAD * INLINE [
Key, Value
A+,12
A,11
A-,10
];

N_STDNT_ENRL_CV:
LEFT KEEP (ACAD_PROG) LOAD
EMPLID,
ACAD_CAREER ,
INSTITUTION ,
STRM ,
CLASS_NBR ,CRSE_GRADE_INPUT,
if(CRSE_GRADE_INPUT='',NULL(),CRSE_GRADE_OFF) as CRSE_GRADE_OFF,
GRADING_SCHEME_ENR, GRADING_BASIS_ENRL
FROM xxx;

App 2:

CRSE_GRADE_INPUT_MAP1:
mapping LOAD
CRSE_GRADE_INPUT_MAPKey, CRSE_GRADE_INPUT_MAPValue
RESIDENT CRSE_GRADE_INPUT_MAP;

drop table CRSE_GRADE_INPUT_MAP;

left JOIN (N_STDNT_ENRL_CV) LOAD
EMPLID,
ACAD_CAREER ,
INSTITUTION ,
STRM ,
CLASS_NBR ,
CRSE_GRADE_OFF,
Dual(CRSE_GRADE_INPUT, ApplyMap('CRSE_GRADE_INPUT_MAP1', CRSE_GRADE_INPUT)) as CRSE_GRADE_INPUT_CORRELATION ,
Resident N_STDNT_ENRL_CV;

Currently my data values are wrong when compared to another app without binary load. Any ideas?

Re: Inline mapping load before binary load?

vishsaggi — Sun, 18 Nov 2018 05:28:05 GMT

CRSE_GRADE_INPUT_MAP:
LOAD * INLINE [
Key, Value
A+,12
A,11
A-,10
];

CRSE_GRADE_INPUT_MAP1:
mapping LOAD
CRSE_GRADE_INPUT_MAPKey, CRSE_GRADE_INPUT_MAPValue
RESIDENT CRSE_GRADE_INPUT_MAP;

drop table CRSE_GRADE_INPUT_MAP;

Your CRSE_GRADE_INPUT_MAP coming from App1 and Mapping load where you are doing a resident load the field name mismatch is happening. Your app1 CRSE grade table has Key and Value as your fields but in your mapping you are using different fields may be you want to use Key, and Value in your resident CRSE_Grade table.

Updated to below :

CRSE_GRADE_INPUT_MAP1:
mapping LOAD
Key, Value
RESIDENT CRSE_GRADE_INPUT_MAP;

Re: Inline mapping load before binary load?

NewToQlik — Sun, 18 Nov 2018 11:43:23 GMT

I have changed them but it still did not work, the data is still incorrect. Any suggestions?

Re: Inline mapping load before binary load?

vishsaggi — Sun, 18 Nov 2018 15:44:34 GMT

I missed this why you doing Left join to same table with resident loading same table. You can just do like in your 2nd app

Noconcatenate

FinalStudentEnrlCV:

LOAD
EMPLID,
ACAD_CAREER ,
INSTITUTION ,
STRM ,
CLASS_NBR ,
CRSE_GRADE_OFF,
Dual(CRSE_GRADE_INPUT, ApplyMap('CRSE_GRADE_INPUT_MAP1', CRSE_GRADE_INPUT)) as CRSE_GRADE_INPUT_CORRELATION ,
Resident N_STDNT_ENRL_CV;

Drop Table N_STDNT_ENRL_CV;

Rename Table FinalStudentEnrlCV to N_STDNT_ENRL_CV;

Re: Inline mapping load before binary load?

NewToQlik — Mon, 19 Nov 2018 05:14:21 GMT

Thank you very much! I have one last problem with my data. Lets say I created a new table to store as below.

N_STDNT_TERM_CV:
LEFT KEEP (ACAD_PROG) LOAD
EMPLID ,
ACAD_CAREER ,
STRM ,
Num((CUR_GPA),'##.00') as CUR_GPA,
Num((CUM_GPA),'##.00') as CUM_GPA
FROM xxx;

MAX_STRM:
LOAD EMPLID ,ACAD_CAREER,INSTITUTION,MAX(STRM) as STRM1 RESIDENT N_STDNT_TERM_CV
where NOT ISNULL(CUM_GPA) group by EMPLID,ACAD_CAREER,INSTITUTION;

JOIN(MAX_STRM)
LOAD EMPLID,ACAD_CAREER,INSTITUTION,ADMIT_TERM RESIDENT ACAD_PROG WHERE EXISTS (ADMIT_TERM);

LATEST_STRM_GPA:
LOAD EMPLID,ACAD_CAREER,INSTITUTION,STRM1,
'20'&left(STRM1,2) as STRM_YEAR RESIDENT MAX_STRM where not match(STRM1,ADMIT_TERM);

LEFT JOIN (LATEST_STRM_GPA)

LOAD EMPLID ,ACAD_CAREER,INSTITUTION,STRM as STRM1,CUM_GPA as CUM_GPA_LAST
RESIDENT N_STDNT_TERM_CV
where exists(EMPLID)and exists(STRM);

drop table N_STDNT_TERM_CV;

drop table MAX_STRM;

I am creating a new table to store EMPLIDs with a max(STRM) GPA, basically I am getting the latest GPA of an EMPLID grouped by the ACAD_CAREER. But I realised that after I binary loaded App1, the data is not recognized. Any thoughts about this behaviour?