Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cawestelaken
New Contributor II

Combining table without summing rows

Hello,

I've been trying to alter an ETL script so that it produces less rows in 1 table.

One of the potential changes would be to the following:

LOAD
%Incident_key
-- extra information --
FROM Datasource_A;

CONCATENATE LOAD
%Incident_key
1 AS _Flag_NewIncident
FROM Datasource_B;

Datasource_A has many more rows and keys than Datasource_B.

The end result of this script would be the sum of the rows in Datasource_A and Datasource_B.

EXAMPLE (the reasoning behind why you'd want to do this isn't logical in the example):

Datasource_A:

%Incident_KeyType
1A
2A
3B
4A
5A
6B
7C
8B
9A
10C

 

Datasource_B:

%Incident_key
6
7
9
10

 

End result:

%Incident_KeyType_Flag_NewIncident
1A .
2A .
3B .
4A .
5A .
6B .
7C .
8B .
9A .
10C .
6.1
7.1
9.1
10.1

 

Is it possible to make the script so that the end result would be:

%Incident_KeyType_Flag_NewIncident
1A.
2A.
3B.
4A.
5A.
6B1
7C1
8B.
9A1
10C1

 

Yours sincerely,
Casper Westelaken

5 Replies
Partner
Partner

Re: Combining table without summing rows

AFAIKT you want to join the two tables more than you want to concatenate them.
LOAD
%Incident_key
-- extra information --
FROM Datasource_A;

JOIN LOAD
%Incident_key
1 AS _Flag_NewIncident
FROM Datasource_B;
cawestelaken
New Contributor II

Re: Combining table without summing rows

I did not realize it was possible to JOIN LOAD.
Thank you very much!

Yours sincerely,

Casper Westelaken

cawestelaken
New Contributor II

Re: Combining table without summing rows

Okey,

Whilst this was the perfect solution for my example. Sadly it doesn't solve my issue the way it should.
This is my fault for not correctly describing the issue.

The Incident_key is a combined key in the first table, where in the second table it is a sole key field.
As follows:

Datasource_A:

%Incident_key%Extra_keyType
111A
112A
113A
211B
311A
312A
422C
531C
541C
523C
512C
656B
632B
712A
843B
923A
954A
1032C

 

Datasource_B:

%Incident_key
2
4
5
7
9

 

End Result using original Concatenate:

%Incident_key%Extra_keyType_Flag_IncidentNew
111A 
112A 
113A 
211B 
311A 
312A 
422C 
531C 
541C 
523C 
512C 
656B 
632B 
712A 
843B 
923A 
954A 
1032C 
2 Example error 1
4 Example error 1
5 Example error 1
7 Example error 1
9 Example error 1

 

End result using Join:

%Incident_key%Extra_keyType_Flag_IncidentNew
111A 
112A 
113A 
211B1
311A 
312A 
422C1
531C1
541C1
523C1
512C1
656B 
632B 
712A1
843B 
923A1
954A1
1032C 

 

Desired End Result:

%Incident_key%Extra_keyType_Flag_IncidentNew
111A 
112A 
113A 
211B1
311A 
312A 
422C1
531C1
541C 
523C 
512C 
656B 
632B 
712A1
843B 
923A1
954A 
1032C 

 

Do you know whether or not this is possible?

Partner
Partner

Re: Combining table without summing rows

How do you know which of the rows with e.g. incident_key = 5, that should be flagged?

%Incident_key%Extra_keyType_Flag_IncidentNew
111A 
112A 
113A 
211B1
311A 
312A 
422C1
531C1
541C 
523C 
512C 
cawestelaken
New Contributor II

Re: Combining table without summing rows

In this example I only used an %Extra_key, but since we're working with a facts table and dimensions setup this is actually multiple keys.

One of these keys is a %Date_key.

The row that should be flagged would the one that coincides with the earliest instance of the Incident.
Datasource_B (The table with the Incident dimension) includes a SysCreated field which during the load statement would be turned into a %Date_Key for loading into the facts table only (to avoid a synthetic key)

Sadly though... Theoretically it is possible for 2 rows in the Facts table to have the same %Incident_key and %Date_key. So I'd rather create something that would simply load it in once per Incident and date combination. All that matters is that the earliest date per incident gets flagged, so that no double flags exist.

So essentially I'm loading the _Flag_IncidentNew into the Facts table using the Incidents table in which the %Incident_key is distinct, where during this load I'm transforming one of the columns in the Incidents table into a %Date_key.

In the actual situation (non-example) every %Incident_key gets flagged once.

I guess my examples were insufficient.

The end result would be:

%Incident_key%Extra_keyType_Flag_IncidentNew
111A1
112A 
113A 
211B1
311A1
312A 
422C1
531C1
541C 
523C 
512C