Problems joining two exactly same tables

Wed, 06 Aug 2014 10:18:53 GMT

Hi,

In continuation with my previous discussion, I have opened this so that you peeps get points for helping me out.

This is where I am : Re: Join not giving the desired results

To answer MartynLloyd last comment in the above post,

here's what I am trying to achieve:

I have 6 input fields, once the user's entered the desired values they submit the data to an excel file. Now because qlikview is wiping out the data entered by the user after reload (only if there are new fields or rows being added to the data model).

So to provide the user with the same values that they had entered, I am trying to the load the latest values from the latest excel file.

To achieve this I need to join new excel data table with the existing main table with inputfields.

I have four columns that act as keys and 6 inputfields columns.

So the main table is 'Items' table m:m relation between items and customers for all weeks.

Heres how the table and data structure look like:

Items: // loaded the first time

Month,

ItemNumber,

Customer Number,

Week

num('0','#,##0.00') as Inputfield1

num('0','#,##0.00') as Inputfield2

num('0','#,##0.00') as Inputfield3

num('0','#,##0.00') as Inputfield4

num('0','#,##0.00') as Inputfield5

num('0','#,##0.00') as Inputfield6

same data for items:

Aug - 2014,

123,

C15,

Week 33 - Aug,

0.00, // set to 0.00 for the first time

0.00,

0.00

// excel data

ExcelData: // this is the data that has values entered by the user

Month,

ItemNumber,

Customer Number,

Week

Column1 as Inputfield1

Column2 as Inputfield2

Column3 as Inputfield3

Column4 as Inputfield4

Column5 as Inputfield5

Column6 as Inputfield6

same data for items:

Aug - 2014,

123,

C15,

Week 33 - Aug,

150,

15,

3.00,

5.50,

1.10,

1000

I just want excel data to be in the items table for the corresponding/ matching columns and rest/unmatched be as it is with inputfields as 0.00

Any ideas?

Maybe a better way?

Thanks

Re: Problems joining two exactly same tables

martynlloyd — Wed, 06 Aug 2014 10:48:26 GMT

My idea:

ExcelData will be your final table, no join or concatenate needed

Items: // loaded the first time

Month & ItemNumber & Customer Number & Week as Key1

Month,

ItemNumber,

Customer Number,

Week

...

// excel data

ExcelData: // this is the data that has values entered by the user

Month,

ItemNumber,

Customer Number,

Week

Column1 as Inputfield1

Column2 as Inputfield2

Column3 as Inputfield3

Column4 as Inputfield4

Column5 as Inputfield5

Column6 as Inputfield6

from Excel

Where Exists(Key1, Month & ItemNumber & Customer Number & Week)

Regards,

Marty.

Re: Problems joining two exactly same tables

Wed, 06 Aug 2014 11:27:19 GMT

Thank you so much dude.. you are awesome.. your solution worked. But also there was a big time data type mismatch issue, thats why it could not match.

Thanks a ton 🙂

topic Problems joining two exactly same tables in QlikView

Problems joining two exactly same tables

Re: Problems joining two exactly same tables

Re: Problems joining two exactly same tables