Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I did not write this script below. Someone before me who is not here anymore wrote it and I am not familiar with joins or writing scripts like this one below. The issue that I am experiencing is that some data from the file F41092 is missing. This client has items that are either sold at an A price or a B price. But for some reason it isn't pulling in all the data. Only some B and A prices are displaying along with only one type of Product Code. Any help is much appreciated.
TempValues:
Load Distinct
T2ITM AS [Short Name],
T2ITM & '_' & T2EFT & '_'& T2EFTE AS [Temp Key],
IF(T2KY='PRDCODE',T2RMK) AS [Product Code],
IF(T2KY='AHASLPRC',T2AMTU)/100 AS [A Sell Price],
IF(T2KY='AHAESCAMT',T2AMTU)/100 AS [A Escrow Amount],
IF(T2KY='BNSLPRC',T2AMTU)/100 AS [B Sell Price],
IF(T2KY='BNESCAMT',T2AMTU)/100 AS [B Escrow Amount],
IF(T2EFT <>0,Date(MakeDate(If(len(T2EFT)>5, 1900+ left(T2EFT ,3), 1900+left(T2EFT ,2))) + (right(T2EFT,3)-1))) as [Effective Date],
IF(T2EFTE <>0,Date(MakeDate(If(len(T2EFTE)>5, 1900+ left(T2EFTE ,3), 1900+left(T2EFTE ,2))) + (right(T2EFTE,3)-1))) as [Ending Date]
;
SQL SELECT T2KY, T2ITM, T2AMTU, T2EFT, T2EFTE, T2RMK
FROM $(....)F41092
;
ItemValues:
LOAD
[Temp Key],
[Short Name],
[A Sell Price],
[Effective Date],
[Ending Date]
Resident TempValues;
Where not IsNull([A Sell Price]);
Left Join (ItemValues)
Load
[Temp Key],
Money([A Escrow Amount]) AS [A Escrow Amount]
Resident TempValues
Where not IsNull([A Escrow Amount])
;
Left Join (ItemValues)
Load
[Temp Key],
Money([B Sell Price]) AS [B Sell Price]
Resident TempValues
Where not IsNull([B Sell Price])
;
Left Join (ItemValues)
Load
[Temp Key],
Money([B Escrow Amount]) AS [B Escrow Amount]
Resident TempValues
Where not IsNull([B Escrow Amount])
;
Left Join (ItemValues)
Load
[Temp Key],
[Product Code] AS [Product Code]
Resident TempValues
Where not IsNull([Product Code])
;
Drop Table TempValues;
Left Join (SalesDetails)
IntervalMatch([Order Date],[Short Name]) LOAD [Effective Date], [Ending Date],[Short Name] Resident ItemValues;
LEFT JOIN (SalesDetails) LOAD
*
Resident ItemValues;
Drop Table ItemValues;
SchoolDates:
Load
DTDCTO & '_' & DTDOCO & '_' & DTKCOO as SalesHeaderKey,
IF(DTEXD2 <>0,Date(MakeDate(If(len(DTEXD2)>5, 1900+ left(DTEXD2 ,3), 1900+left(DTEXD2 ,2))) + (right(DTEXD2,3)-1))) as [Extra Date 2]
;
SQL SELECT DTDCTO, DTDOCO, DTKCOO, DTEXD2
FROM $(.....)F8301DT
WHERE DTKCOO='$(CompanyLimit)' AND DTEXD2>0
;
Please load your application
Can you check if the missing items from F41092 have selling price A or B?
If they have price B -> you will not bring them across to ItemValues table -> they will not be taken in the first resident load you are performing as they will not have price A assigned. Then in the next step when you are performing resident load where selling price B is not null -> you will find those elements there, but they will not be left joined into Item Values table (as left join between these tables will take only the rows from Table B that already exist in Table A).
Maybe you should think about replacing
Left Join (ItemValues)
Load
[Temp Key],
Money([B Sell Price]) AS [B Sell Price]
Resident TempValues
Where not IsNull([B Sell Price])
;
with
Concatenate
Load
[Temp Key],
Money([B Sell Price]) AS [B Sell Price]
Resident TempValues
Where not IsNull([B Sell Price])
;
But as the person before me mentioned - can you upload your script?
Could you attach the sample application?
Thank you for the reply! Unfortunately, I cannot load a sample of my application. I looked in my datasouce and the items that should have either A prices or B prices or both are there... it isn't missing so something is wrong with my script. I can pull in just the first table (TempValues) without any joins and everything does display however, for each 2nd item number (short name) the price, escrow amount, and product code appear on different lines. I have included an example of one item number with multiple lines.
I want the table to look like this:
Pl Chk if date fields are populated properly and having consistent format?
Did you try to replace that left join with outer join? That should help you to bring across info about both prices - A and B. So it should look like the script in the attached txt. Let me know if it's any better now. Good luck!
Edit - I'm starting to think that OUTER JOIN is what you are looking for, not concatenate, apologies for the confusion.
Thanks! I tried it out and it is getting closer to what I want however now the item number is displaying if it has both A AND B prices but what it is not displaying when the item number just has B prices and no A prices. Should I do an outer join for each price, escrow amount, and product code?
It's really hard to determine without the sample app I'm afraid.
What table of these 4 is the biggest one?
Not sure if that will be of any help to you, but see the qvw - maybe ApplyMap would be the most sufficient way to bring across some of the columns from different tables?