Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problems joining two exactly same tables

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,

0.00,

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

1 Solution

Accepted Solutions
martynlloyd
Partner - Creator III
Partner - Creator III

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.

View solution in original post

2 Replies
martynlloyd
Partner - Creator III
Partner - Creator III

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.

Not applicable
Author

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 🙂