Problem with "join", please help.

[#1]

Hello and thank you for reading!

Here is my problem: I am developing an app that requires for me to add a flag of accepted combination of products and clients.

This is a cost app, that after making all the calculations and getting a result, that result must be reviewed using this flag,( I might no be too specific, if that is the case please ask me more), so my solution was to add an inline table with the accepted combination of product-client and also load a number "1" as the so called flag; but I cant merge this two tables together with a left join because my inline does not have months and my result table does, so my solution was to just join the month field from the result table to the inline but it didn't worked (it kept repeating values), so my question is: How do I add a new unique value (months) in my inline table with out getting repetition of the inline data???

Here are some tables explaining my problem:

Keyresult	ClientProduct	MonthYear	Data
Jan 2015|Walmart|Xboxone	Walmart|Xboxone	Jan 2015	1000
Jan 2014|Walmart|Playstation4	Walmart|Playstation4	Jan 2014	5
Jan 2015|Walmart|Xboxone	Walmart|Xboxone	Jan 2015	1000
March 2015|Costco|Playstation4	Costco|Playstation4	Marh 2015	500

The above table represents my results table, where the combination "Walmart|Playstation4" is an error, this is because the company does not sale Playstation4 to Walmart,, but they do sale that product to Costco. The reason of this error is that the people filling up orders have two different columns to choose from when filling an order (Client and Product), and some times they do it wrong.

The inline table with the flag I mention before would look like this:

Rowno	AcceptedClientProd	FlagAccepted
1	Walmart|Xboxone	1
2	Costco|Playstation4	1

As you can see I need the month to do a correct "left join", what I got when joining the month from the results table to this one was something like this:

Rowno	MYAcceptes	AcceptedClientProd	FlagAccepted
1	Jan 2014	Walmart|Xboxone	1
1	Jan 2014	Walmart|Xboxone	1
1	Jan 2014	Walmart|Xboxone	1
1	Jan 2014	Walmart|Xboxone	1
1	Jan 2014	Walmart|Xboxone	1
1	Jan 2014	Walmart|Xboxone	1
1	Jan 2014	Walmart|Xboxone	1
1	Jan 2014	Walmart|Xboxone	1

This of course this (the repetition) happens for every month and for every combination of accepted client and product, this is only and example, the real one has periods from 2013 and when this happens my computer frozes.

Please if you know how to help me, i would be really grateful for your help, thank you.

[cjohnson]

Hi Jesus,

Based on your example - it doesn't seem as if you need to include month in your inline table (unless a flag is acceptable for one month but not the next for the same AcceptedClientProd). In any case you should be able to do a LEFT Join on AcceptedClientProd instead of doing it on KeyResult.

You may also do a mapping load instead.

Where:

MaptoFlag:

MAPPING

LOAD (INLINE)

     AcceptedClientProd,

     1

Then you would load in your data and use applymap with the AcceptedClientProd field.

I'll see if I can draft up an example of this to show you.

Thanks,

Camile

[#3]

Hello Camile and thank you for your answer,

You are right, the combinations are the same of every moths, but I wanted to include the month in the table just in case they would ask me to change it in the future. what I mean is that right now the company might not be selling play stations to walmart, but if they start doing it in the next month, then the combination would be an accepted one and the month would play an important role.

I would try to do the applymap thing, I actually didnt think about that 😛

I will wait for you example too though

[cjohnson · Accepted solution]

Both examples are attached (applymap and left join).

Just switch the exit script to get either option.

Let me know if this helps.

Thanks,

Camile

[maxgro]

like this?

a:

LOAD Keyresult,

     ClientProduct,

     MonthYear,

     Data,

     RowNo() as Id

FROM

[https://community.qlik.com/thread/185025]

(html, codepage is 1252, embedded labels, table is @1);

b:

LOAD Rowno,

     AcceptedClientProd as ClientProduct,

     FlagAccepted

FROM

[https://community.qlik.com/thread/185025]

(html, codepage is 1252, embedded labels, table is @2);

[#6]

The mapping worked, but I have an other question:

Now I need to sum all the errors and add them to the result table as a dimension called: "others" under the column of Client|Product. By any chance you happen to know how to do that agregation and then add the new dimension?

Thank you.

[cjohnson]

I'm not sure if I follow - is it that you want a new field in your data that has all the appropriate/non flagged records and then all other records to be grouped in "Others".

If so change the script accordingly (adding an if statement):

Or is it that you actually want to "sum" the errors?

Camile