topic Concatenate and left join in a loop in QlikView

Concatenate and left join in a loop

martyn_birzys — Wed, 22 Jul 2015 09:56:46 GMT

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;

Re: Concatenate and left join in a loop

sunny_talwar — Wed, 22 Jul 2015 10:14:18 GMT

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

Re: Concatenate and left join in a loop

sasiparupudi1 — Wed, 22 Jul 2015 12:27:18 GMT

Try qualyfying your common fields using the

qualify statement

quality [Company Name],[PO Number];

Concatenate //the final table

PO:
LOAD *
Resident t_PO;

or create a composite key in your sub tables

HTH

Re: Concatenate and left join in a loop

mjayachandran — Wed, 22 Jul 2015 13:34:45 GMT

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.

Re: Concatenate and left join in a loop

sgrice — Wed, 22 Jul 2015 13:52:01 GMT

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;

Re: Concatenate and left join in a loop

martyn_birzys — Fri, 24 Jul 2015 09:25:31 GMT

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

Re: Concatenate and left join in a loop

martyn_birzys — Fri, 24 Jul 2015 09:30:49 GMT

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.

Re: Concatenate and left join in a loop

martyn_birzys — Fri, 24 Jul 2015 09:34:15 GMT

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.

Re: Concatenate and left join in a loop

sgrice — Fri, 24 Jul 2015 09:38:35 GMT

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.

Re: Concatenate and left join in a loop

martyn_birzys — Fri, 24 Jul 2015 09:43:11 GMT

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.

Re: Concatenate and left join in a loop

sgrice — Fri, 24 Jul 2015 10:06:02 GMT

To show your appreciation can you Set Action HELPFULL or Press Like on anything that you like or fine helpful

This really helps make long Discussion easier to read.

Re: Concatenate and left join in a loop

martyn_birzys — Fri, 24 Jul 2015 13:26:01 GMT

You are right, I should mark my comments as helpful!