I am having an issue with Joins.

[neena123]

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

;

[neena123]

Well they are all coming from the same table within a file from oracle. When I bring it over to qlik each item number displays their price, escrow, and product code on different lines.

[sasiparupudi1]

please try  the bold parts in your script

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

;

NoConcatenate

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;

NoConcatenate

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

;

[neena123]

Thanks for the reply! But it didn't bring all the data in.

[sasiparupudi1]

Hi Neena

if you commented out the below section, do you see full data set?

NoConcatenate

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;

[neena123]

Thanks but it gave the same result. Just to clarify this is what is happening:

This is how I want it to look:

[ChiragPradhan]

Does this help?

Test:

LOAD * Inline

[ItemNumber, BPrice, APrice, BEscrow, AEscrow

132, 0.3,,,

132, ,0.4,,

132, ,,1.23,

132, ,,,2

156, 2,,,

156, ,,3,

278, ,1.4,,

278, ,,8,

394, ,5,,

394, ,,,3

851, 0.8,,,

851, ,,,

851, ,,,0.3

];

Final:

LOAD ItemNumber, Max(BPrice) AS BPrice

Resident Test

WHERE Len(BPrice) > 0

Group By ItemNumber

;

JOIN(Final)

LOAD ItemNumber, Max(APrice) AS APrice

Resident Test

WHERE Len(APrice) > 0

Group By ItemNumber

;

JOIN(Final)

LOAD ItemNumber, Max(BEscrow) AS BEscrow

Resident Test

WHERE Len(BEscrow) > 0

Group By ItemNumber

;

JOIN(Final)

LOAD ItemNumber, Max(AEscrow) AS AEscrow

Resident Test

WHERE Len(AEscrow) > 0

Group By ItemNumber

;

DROP Table Test;

Regards

Chirag

[neena123]

The data is coming from Oracle it is not an inline load. I cannot load a sample application so that above is my example of what happens when the data is loaded in with no joins at all and the table right below it is what I want.

[neena123]

Thanks for the reply though!

[ChiragPradhan]

The inline table is used for test only. the data you are loading can be loaded in a resident table and then use the script I provided or store it in a qvd and then use the script. Unless I am missing something here. !

Regards

Chirag.

[neena123]

It doesn't work. It keeps saying aggregation error...Thanks for trying!