Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

neena123
New Contributor II

I am having an issue with Joins.

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

;

19 Replies
sasiparupudi1
Honored Contributor III

Re: I am having an issue with Joins.

Please load your application

kuczynska
Contributor III

Re: I am having an issue with Joins.

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?

Arjunarao
Honored Contributor II

Re: I am having an issue with Joins.

Could you attach the sample application?

neena123
New Contributor II

Re: I am having an issue with Joins.

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.

Example  1.PNG

I want the table to look like this:

Example 2.PNG

Digvijay_Singh
Honored Contributor III

Re: I am having an issue with Joins.

Pl Chk if date fields are populated properly and having consistent format?

kuczynska
Contributor III

Re: I am having an issue with Joins.

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.

neena123
New Contributor II

Re: I am having an issue with Joins.

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?

kuczynska
Contributor III

Re: I am having an issue with Joins.

It's really hard to determine without the sample app I'm afraid.

What table of these 4 is the biggest one?

kuczynska
Contributor III

Re: I am having an issue with Joins.

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?

Community Browser