topic Re: Data Model Issue in QlikView

Data Model Issue

Tue, 01 Sep 2015 07:20:04 GMT

Hi All,

I have an issue with my model.

I have attached 2 models

1. Test

2. Data Model

In the test i have loaded 2 files where you can see the status, stage and states are mapping with the amount.

Similarly i have loaded in Data model (Which is my final model with all the tables) where there is no mapping between status(S_status), stage(S_stage) and state(S_state) with amount(pay_amount)

Please let me know if you need any other information.

Regards,

Keerthi KS

Re: Data Model Issue

parthakk — Tue, 01 Sep 2015 07:38:48 GMT

Hi,

Where is amount field in your datamodel? I mean which table holds this field?

Re: Data Model Issue

Tue, 01 Sep 2015 08:03:40 GMT

pay_amount is the amount field.

Its in Payment_Temp table under receivables tab in the script

Re: Data Model Issue

Anonymous — Tue, 01 Sep 2015 09:09:20 GMT

Keerthi,

I think you forget to load you mapping tables as mapping load .

Check your mapping table :

You need to load these tables as mapping table

Like you have done for

MapBlock_CBPID:

Mapping

LOAD Distinct P_ID,B_ID Resident Projects where Len(Trim(CPB_ID))<>0;

/***************************************************************

here is the problem -

Map_State_Payment:

LOAD

uniqueid,

state

FROM

[$(vPath)\tesaleorde.qvd]

(qvd);

Map_Stage_Payment:

LOAD

uniqueid,

stage

FROM

[$(vPath)\tesaleorde.qvd]

(qvd);

Map_Status_Payment:

LOAD

uniqueid,

status

FROM

[$(vPath)\tesaleorde.qvd]

(qvd);

Map_State_Payment:

LOAD

uniqueid,

state

FROM

[$(vPath)\tesaleorde.qvd]

(qvd);

Map_Stage_Payment:

LOAD

uniqueid,

stage

FROM

[$(vPath)\tesaleorde.qvd]

(qvd);

Map_Status_Payment:

LOAD

uniqueid,

status

FROM

[$(vPath)\tesaleorde.qvd]

(qvd);

Re: Data Model Issue

parthakk — Tue, 01 Sep 2015 09:18:03 GMT

Hi Keerti,

If i am not wrong u are expecting mapping between status(S_status), stage(S_stage) and state(S_state) with amount(pay_amount), right? U have dropped table Payment_Temp and before dropping u have not saved pay_amount field in any other table.. So how can there be any mapping.

Thanks,

Partha Kulkarni

Re: Data Model Issue

Tue, 01 Sep 2015 09:20:53 GMT

Yes you are right.

Where should i drop the table ?

Regards,

Keerthi KS

Re: Data Model Issue

Tue, 01 Sep 2015 09:21:49 GMT

I did mapping. But without applymap also there should be mapping right ?

Re: Data Model Issue

parthakk — Tue, 01 Sep 2015 09:27:43 GMT

Hi,

First need to know the reason for table dropping. And if u want it to drop then u must have pay_amount field somewhere in other tables.. If u drop table then its like deleting that field hence no mapping at all.

Thanks,

Partha Kulkarni

Re: Data Model Issue

Tue, 01 Sep 2015 09:32:17 GMT

But there is a field(Pay_amount) even after dropping the table Payment_Temp.

Concatenate(Projects)

LOAD *,if(Len(Trim(S_ID))<>0,Enq_ID_Temp,ApplyMap('MapOppEnqID',Map_Opp_ID)) as Enq_ID,Receivable_Date as Unit_Link Resident Payment_Temp;

Here we are concatenating with Projects and then dropping it. so Mapping should be there.

This is developed by someone else and currently i am working on it.

Re: Data Model Issue

Anonymous — Tue, 01 Sep 2015 09:42:03 GMT

actually your mapping is with uniqueid column not with Stage.

So when you will select SID or uniqueid you will get the amount but for Stage you will not as in concatenate you are not making the column name same for Stage.

Thanks

BKC

Re: Data Model Issue

Tue, 01 Sep 2015 09:47:55 GMT

But saleorder(SID) is mapped to Stage, state and status. Which inturn has to be mapped with payamount. These mappings can be seen in test.qvw

Saleorder is mapped to Stage, state and status.

Status stage and state is mapped to payamount

Re: Data Model Issue

parthakk — Tue, 01 Sep 2015 10:14:21 GMT

Hi,

In receivables line no:57 u used resident projects but i found nowhere as projects table earlier.. For doing any concatenate you should have a table with us, am i right?

Thanks,

Partha Kulkarni

Re: Data Model Issue

Tue, 01 Sep 2015 10:18:14 GMT

Projects table is in Projects tab. The second tab. Table exists

Re: Data Model Issue

parthakk — Tue, 01 Sep 2015 11:05:22 GMT

Hi Keerthi,

Earlier your mapping was between stage,status and state and that exists even now.. If u select s_status or so it wont work. For that u need to check your mapping table. You are fetching it from 'Map_State_Payment' but i doubt on that.

I could not find your mapping load at all... Make sure that after u do apply map u get data associated between s_status and UNIQUEID.

Thanks,

Partha Kulkarni

Re: Data Model Issue

Tue, 01 Sep 2015 11:48:58 GMT

S_Status is the alias of status.

Currently mapping is working with pay_amount and s_status. But without mapping it wont work ?? because S_ID is already mapped with pay_amount and S_ID with S_status.