Association issue

[Bharathi09]

Hi all,
I have query in association issue

I have script like below

INPUT_DATA:

LOAD type,

PlantYear,

[JIT Route] as Lane,

YEAR as Year,

PlantCode,

MODE,

'Standards' as "Record Type",

Right([JIT Route],Len([JIT Route])-Index([JIT Route],'-',1)) as destination,

SubField([JIT Route], '-', 1) as origin,

[COST] as "Standard Cost",

[TOTAL STANDARD] as "Standard Value

LPM_AUG,

LPW_AUG,

LPM_SEPT as LPM_SEP,

LPW_SEPT as LPW_SEP,

LPM_OCT,

LPW_OCT,

LPM_NOV,

LPW_NOV,

LPM_DEC,

LPW_DEC,

LPM_JAN,

LPW_JAN,

LPM_FEB,

LPW_FEB,

LPM_MAR,

LPW_MAR,

LPM_APR,

LPW_APR,

LPM_MAY,

LPW_MAY,

LPM_JUN,

LPW_JUN,

LPM_JUL,

LPW_JUL

FROM 2023 Final.xlsx;


Olddata:

load *,

origin & ' & ' & destination & ' & ' & Lane as KeyCol

Resident INPUT_DATA;

Drop table INPUT_DATA;

CrossTableLPM:

CrossTable(MonthLPM, STD_LPM, 2)

Load

KeyCol,

Year,

LPM_JAN as JAN, LPM_FEB AS FEB, LPM_MAR AS MAR, LPM_APR AS APR, LPM_MAY AS MAY, LPM_JUN AS JUN, LPM_JUL AS JUL, LPM_AUG AS AUG, LPM_SEP AS SEP,

LPM_OCT AS OCT, LPM_NOV AS NOV, LPM_DEC AS DEC

Resident Olddata;

CrossTableLPW:

CrossTable(MonthLPW, STD_LPW, 2)

Load

KeyCol,

Year, LPW_JAN AS JAN, LPW_FEB AS FEB, LPW_MAR AS MAR, LPW_APR AS APR, LPW_MAY AS MAY, LPW_JUN AS JUN,

LPW_JUL AS JUL, LPW_AUG AS AUG, LPW_SEP AS SEP,

LPW_OCT AS OCT, LPW_NOV AS NOV, LPW_DEC AS DEC

//origin & ' & ' & destination & ' & ' & Lane as OrigDestLane

Resident Olddata;

//Drop table INPUT_DATA;

STD_COST:

LOAD *,

Date(Date#(Year & Right(MonthLPM, 3), 'YYYYMMM'),'YYYY-MM') as "Activity Date"

Resident CrossTableLPM;

Drop Table CrossTableLPM;


Concatenate(STD_COST)

LOAD

*,Date(Date#(Year & Right(MonthLPW, 3), 'YYYYMMM'),'YYYY-MM') as "Activity Date"

Resident CrossTableLPW;

DROP Fields Year, MonthLPW, MonthLPM;

Drop Table CrossTableLPW;

Exit Script;



Actually, here with above script when i am selecting particular lane standard cost is filtering, but when I select particular activity date for that lane still showing same standard cost value, but standard cost not changing according to activity date 

Please let me know, if am doing anything rong here and help me

Thanks!!

[Anil_Babu_Samineni]

@Bharathi09 Can you show us the data model and associative how that is?

And, If i look you are creating same field name from CrossTableLPM and CrossTableLPW. So, I would suggest to create with Link table in such scenarios to have link properly. 

And, Lastly not sure where did you use this key "origin & ' & ' & destination & ' & ' & Lane as KeyCol". the best practice to use some auto keys using Autonumber() to map them correctly, 

[Bharathi09]

Hi actually, Before I used to have everything in std_cost table

But when I am doing sum(cost) in frontend it's duplicating values, it's repeating
So I created one column as KeyCol for main table and created Key between Std_cost Olddata table

I don't know it's correct way or not but I wanted to not duplicate values...
My input table:

Dest	COST	LPM_JAN	LPW_JAN	LPM_FEB	LPW_FEB
72-02452AH	4.98	6	5	6	4
72-02452AH	9.11	8	6	7	5
72-02452AH	0.00	0	3	0	4

My output table:

Dest	COST	Activity Date	LPM	LPW
72-02452AH	4.98	2023-01	6	5
72-02452AH	9.11	2024-01	8	6
72-02452AH	0	2023-01	0	3
72-02452AH	4.98	2023-01	6	4
72-02452AH	9.11	2024-01	7	5
72-02452AH	0	2023-01	0	4

Here cost is giving duplicates, so when doing some(cost) it'll be giving msimatches,
how to solve this, I want to do with associations of two tables by using key

can you please let me know how to implement this?

Thanks in advance!!!
please reply back

[Anil_Babu_Samineni]

@Bharathi09 Can you please share sample QVF file to check

[Bharathi09]

First table is main table, after that those are crosstables I created to get Unpivotted output

Thanks!!

[Bharathi09]

Hello ,

With my below code I am seeing like this....

INPUT_DATA:

LOAD type, 

     PYear, 

     [JIT] as Lane, 

     YEAR as Year, 

     Code, 

     MODE,

     'Standards' as "Record Type",

     REPLACE(LTRIM(REPLACE(PlantCode, '0', ' ')), ' ', '0') as c_loc_bill2,

     Right([JIT],Len([JIT])-Index([JIT],'-',1)) as destination,  

SubField([JIT ], '-', 1) as origin,

     [Standard Cost], 

     [BASE RATE] as STD_Base, 

     [ AVG], 

     [C AVG], 

     [other AVG], 

     [TOTAL STANDARD] as "Standard ", 

     LPM_AUG, 

     LPW_AUG, 

     LPM_SEPT as LPM_SEP, 

     LPW_SEPT as LPW_SEP, 

     LPM_OCT, 

     LPW_OCT, 

     LPM_NOV, 

     LPW_NOV, 

     LPM_DEC, 

     LPW_DEC, 

     LPM_JAN, 

     LPW_JAN, 

     LPM_FEB, 

     LPW_FEB, 

     LPM_MAR, 

     LPW_MAR, 

     LPM_APR, 

     LPW_APR, 

     LPM_MAY, 

     LPW_MAY, 

     LPM_JUN, 

     LPW_JUN, 

     LPM_JUL, 

     LPW_JUL

FROM 2023.xlsx

Olddata:

load *,

origin & ' & ' & destination & ' & ' & Lane as KeyCol

Resident INPUT_DATA;

Drop table INPUT_DATA;

CrossTableLPM:

CrossTable(MonthLPM, STD_LPM, 2) 

Load

     Year,

KeyCol,

    LPM_JAN as JAN, LPM_FEB AS FEB, LPM_MAR AS MAR, LPM_APR AS APR, LPM_MAY AS MAY, LPM_JUN AS JUN, 

    LPM_JUL AS JUL, LPM_AUG AS AUG, LPM_SEP AS SEP,

    LPM_OCT AS OCT, LPM_NOV AS NOV, LPM_DEC AS DEC

Resident Olddata;

CrossTableLPW:

CrossTable(MonthLPW, STD_LPW, 2) 

Load

   Year,

   KeyCol,

       LPW_JAN AS JAN, LPW_FEB AS FEB, LPW_MAR AS MAR, LPW_APR AS APR, LPW_MAY AS MAY, LPW_JUN AS JUN,

    LPW_JUL AS JUL, LPW_AUG AS AUG, LPW_SEP AS SEP,

    LPW_OCT AS OCT, LPW_NOV AS NOV, LPW_DEC AS DEC

//origin & ' & ' & destination & ' & ' & Lane as OrigDestLane

Resident Olddata; 

//Drop table INPUT_DATA;

STD_COST:

LOAD *,

Date(Date#(Year & Right(MonthLPM, 3), 'YYYYMMM'),'YYYY-MM') as "Activity Date"

Resident CrossTableLPM;

Drop Table CrossTableLPM;

Concatenate(STD_COST)

LOAD

*,

Date(Date#(Year & Right(MonthLPW, 3), 'YYYYMMM'),'YYYY-MM') as "Activity Date"

Resident CrossTableLPW;

DROP Fields Year, MonthLPW, MonthLPM;

Drop Table CrossTableLPW;

Exit Script;

Please reply back, my values are duplicating if I do sum(standard cost), it's duplicating and if I do distinct sum, then values are missing
Please help

Here also when selecting activity date values of standard cost are not changing in kpi's giving same value for whole lane number