Concatenate and left join in a loop

[martyn_birzys]

I've read numerous posts and tried various ways of getting this work, but I either get blank tables (NULLs I presume), or three duplicate tables with massive synthetic keys. How do I solve this?

PO: //table placeholder, as I'm concatenating later
LOAD * Inline 
[temp1, temp2
1, 1 ];


for each CoName in 'Company1', 'Company2', 'Company3'


t_PO:  //temporary table
LOAD POPOrderReturnID,
  '$(CoName)' as [Company Name],
    DocumentNo as [PO Number],
    DocumentStatusID,
    DocumentPrintStatusID, //+10 more fields
    TotalGrossValue;
SQL SELECT * FROM "$(CoName)".dbo.POPOrder;


Concatenate
t_PO: //concatenate archived data
LOAD POPOrderReturnID,
  '$(CoName)' as [Company Name],
    DocumentNo as [PO Number],
    DocumentStatusID,
    DocumentPrintStatusID, 
    TotalGrossValue;
SQL SELECT * FROM "$(CoName)".dbo.POPOrderArchive;


left join
t_PO:  //replaces ID with Name
LOAD DocumentStatusID,
    Name as "PO Status";
SQL SELECT DocumentStatusID,
    Name
FROM "$(CoName)".dbo.DocumentStatus;


drop field DocumentStatusID;


left join
t_PO: //replaces another ID with Name
LOAD DocumentPrintStatusID,
    Name as "PO Print Status";
SQL SELECT DocumentPrintStatusID,
    Name
FROM "$(CoName)".dbo.DocumentPrintStatus; //+2 more joins like this


drop field DocumentPrintStatusID;


Concatenate  //the final table
PO:
LOAD *
Resident t_PO;


DROP Table t_PO;


NEXT


DROP Fields temp1, temp2;

[sunny_talwar]

May be try this:

FOR each CoName in 'Company1', 'Company2', 'Company3' 

t_PO:  //temporary table 

LOAD POPOrderReturnID,

          '$(CoName)' as [Company Name], 

           DocumentNo as [PO Number], 

          DocumentStatusID, 

          DocumentPrintStatusID, //+10 more fields

          TotalGrossValue; 

SQL SELECT * FROM "$(CoName)".dbo.POPOrder; 

Concatenate 

t_PO: //concatenate archived data 

LOAD POPOrderReturnID,

          '$(CoName)' as [Company Name], 

          DocumentNo as [PO Number],

          DocumentStatusID, 

          DocumentPrintStatusID,  

          TotalGrossValue; 

SQL SELECT * FROM "$(CoName)".dbo.POPOrderArchive;

left join

Temp1:  //replaces ID with Name 

LOAD DocumentStatusID,

          Name as "PO Status"; 

SQL SELECT DocumentStatusID,

                      Name 

FROM "$(CoName)".dbo.DocumentStatus;

DROP Field DocumentStatusID;

left join

Temp2: //replaces another ID with Name 

LOAD DocumentPrintStatusID,

          Name as "PO Print Status"; 

SQL SELECT DocumentPrintStatusID, 

                     Name 

FROM "$(CoName)".dbo.DocumentPrintStatus; //+2 more joins like this 

DROP Field DocumentPrintStatusID; 

NEXT;

PO:

NoConcatenate

LOAD *

Resident t_PO;

Left Join(Temp_PO)

LOAD *

Resident Temp1

Left Join(Temp_PO)

LOAD *

Resident Temp2

DROP Tables t_PO, Temp1, Temp2

[sasiparupudi1]

Try qualyfying your common fields using the

qualify statement

quality [Company Name],[PO Number];

Concatenate  //the final table 

1. PO: 
2. LOAD * 
3. Resident t_PO; 

or create a composite key in your sub tables

HTH

[mjayachandran]

One simple way would be to Store t_PO into Company wise qvd's and drop t_PO; Go to next loop.

Later read all the qvds.

[sgrice]

Why are you getting qlikview to do what sql does best

get SQL to Left Join The Table

Example Below Based on MS SQL:-

LOAD *;

SELECT * FROM "$(CoName)".dbo.POPOrderArchive POP

left join "$(CoName)".dbo.DocumentStatus STATUS on POP.DocumentStatusID=STATUS.DocumentStatusID

left join "$(CoName)".dbo.DocumentPrintStatus PSTATUS on POP.DocumentPrintStatusID=PSTATUS.DocumentPrintStatusID;

[martyn_birzys]

I have thought of this approach, but would it not be extremely slow to reload?

[martyn_birzys]

You're probably right, but

1. I was taught in a puristic Qlikview way where you load tables 'as is', and then do all processing in Qlikvliew.

2. I am not as confident in SQL, which would mean I'd have to test script in SQL Management Studio, and then come back to Qlikview. This may not be even feasible in some customer sites.

[martyn_birzys]

That's nice alternative to '$(CoName)' as [Company Name] variable, but the issue is the way Qlikview handles concatenate and left join in a loop - I would still have the same problems, IMO.

[sgrice]

True but it's horses for courses. Qlikview Joining is single treaded [in the main] so can be slow, especially on unsorted data.

Pure Qlikview  way would be to do it for one DB [Company] store it out and drop, Repeat for each and then load the stored QVD's. This would also most likely be the quickest way in qlikview.

[martyn_birzys]

I've tried this approach too, and for some reason I got it working in Qlik Sense, but not in Qlikview. Is there a difference the way the engine handles it?

I've finally got it working by creating a loop for each table, and then concatenating to the main table after the loop. Concatenating two tables with exact structure works fine within a loop, but not left joining or concatenating to the main table. There is a lot of redundancy doing it this way, but at least it joins and concatenates data correctly.