Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

andriesb
Contributor

concat two different tables to one new table (different field names) for single projection

I want to concatentating two tables : the extra information should create duplicated records...

I have two tables where the second tabel contains ‘extra’ information within a 1:n relation.

The first table contains the vehicle general information

vehicle

numberColor
Alfa12-34-56White
BMW55-66-77Blue


The second table contains the inspections:

 

Numberinspection typesLast inspectionnext inspectionHeader 5
12-34-56Yearly inspection01-01-201001-01-2011
12-34-56Yearly inspection05-01-201105-01-2012
12-34-564 years inspection06-05-201006-05-2014
55-66-77Yearly05-05-201105-05-2012

I would like to merge these two tabels into one where the outcome should be :

vehiclenumberUnique inspection typesLastNext
Alfa12-34-56Yearly inspection05-01-201105-01-2012
Alfa12-34-564 years inspection06-05-201006-05-2014
B55-66-77Yearly05-05-201105-05-2012


It should merge from the original database the unique different inspection types (1:n relation) and project only the last known inspection date info my overview table: this means i could filter (by date) only the inspection and inspection types to still be done within the selected month

as an example, the resulting merged table should be shown in a 'flat' perspective:

Clipboard01.jpg

How could I join information into a resulting table whereby the resulting table creates extra (duplicate) records to contain the information.

In words the soluton could be : iterate through the cars list ; search for the unique types of inspections avaiable for this car, select by mapping the max(first date) from the database...

I'm used to program, but to add all these conditions into a single "load" seems to be quite difficult.

Some ways to solve this by providing pseudo code or step by step info would be appreciated.

1 Solution

Accepted Solutions
dennisnet
Valued Contributor III

Re: concat two different tables to one new table (different field names) for single projection

Hi Andries,

Take a look at the attached file.

Laat me even weten of dit de oplossing biedt is wat je zoekt :-)


Succes,

Dennis.

3 Replies
dennisnet
Valued Contributor III

Re: concat two different tables to one new table (different field names) for single projection

Hi Andries,

Take a look at the attached file.

Laat me even weten of dit de oplossing biedt is wat je zoekt :-)


Succes,

Dennis.

giakoum
Honored Contributor II

Re: concat two different tables to one new table (different field names) for single projection

check out the attached file

lironbaram
Honored Contributor II

Re: concat two different tables to one new table (different field names) for single projection

hei

check this example

all the calculating is benig done in the script

the result is one table

Community Browser