$Syn table causing problems after loading extra table

[#1]

Hi.  I have a simple database working well -  relating to sales leads.  I've attached the before and after table view (see Word doc attached) resulting from adding the forecast table.  The forecast table is a flat table having been converted from a cross-table in Excel.  I do understand that this table has 3 fields that uniquely identifies each record, and this is causing the problem.  Any tips on how to change my data structure or how to overcome the Syn table that is now causing some of the filter fields to not work right will be much appreciated.  Also attached a sample of the excel "Forecast" table that is causing the problems.

Many thanks

[reshmakala]

You may concatenate tables with same field names appropriately as per your data

[#3]

Hi there,

Please create a key in your forecast table like

Load Autonumberhash128(Item,Franchise,PlannedClosedDate) as Key,

ForecastQty

From Source;

now create the same key in LeadList table as well so that these two tables will be linked through Key field, thereby eliminating synthetic key.

Thanks

[vinafidalgo]

Did you solved the problem?
If not, please upload the QVW.

[marchoctober]

Hi! Your question is related to data modeling.

So you need to learn some basic techniques how to prevent, avoid or remove synt keys.

1) Classic, renaming the fields in a case when it makes sense.

2) Restructuring data model in way to leave one (or more) fact table with distinct fields.

3) Using link table: a table that links other tables and contains only the keys generated (like an answer above with hash)

4) Other methods.

In your case I would advise the following:

Simple way - use Qualify & Unqualify script statements (use Help for syntax)

A little bit advanced, as said previously, use Concatenate.

Also, refer to official QlikView Self Tutorial ! There you will find everything in Basic you need to understand how to solve the issue.

See tutorial in attachment (it also contains all qvw files and data sources to game with)

Have fun!

[#6]

Hi Ajay,  I cannot get the syntax right as per your advise...a syntax error comes up.  Can you provide the right syntax for autonumberhash128 please.

[#7]

Hi Vinicius, I have personal edition, so not sure if this will help if you send it back again?  But here is the file, maybe you can assist?  many tx

[#8]

Please try this code.

LOAD AutoNumberHash128(Item,Franchisee,[Planned CloseDate]) as Key,

  ProspectName,

     [Prospect Area/Suburb],

     Contact,

     Position,

     [Description/Comment],

     Item,

     Quantity,

     Value,

     DealType,

     Stage,

     Probability,

     [Won/Lost/Open],

     [Unique ID],

     Franchisee,

     [Planned CloseDate],

     [Updated on Thursday Date],

     FirstContactDate, 

     ProspectIndustry,

     LeadOrigin,

     Interval([Planned CloseDate]-FirstContactDate, 'd') as CloseDays

FROM

(ooxml, embedded labels, table is [Lead List]);

LOAD Item,

     ItemCategory,

     [Product/Equipment]

FROM

(ooxml, embedded labels, table is [Master Data Locked]);

LOAD

     AutoNumberHash128(Item,Franchisee,[Planned CloseDate]) as Key,

     ForecastQty

FROM

(ooxml, embedded labels, table is EquipmentForecast);

Thanks

[#9]

Hey Ajay,

Thanks for the reply. Your script removed the Syn table/s completely.  So now the relational tables view looks great.  BUT, the data is not right. The "ForecastQty" does not display in any of the tables, and if you click on any of the other fields to sort the data (like franchisee) it shows no records at all. I've attached the table view, but I'm not sure how to get this one working?  Have you got any more idea's?  tx

[#10]

Hey can you try this:

LeadList:

LOAD AutoNumberHash128(Item,Franchisee,[Planned CloseDate]) as Key,

  ProspectName,

     [Prospect Area/Suburb],

     Contact,

     Position,

     [Description/Comment],

     Item,

     Quantity,

     Value,

     DealType,

     Stage,

     Probability,

     [Won/Lost/Open],

     [Unique ID],

     Franchisee,

     [Planned CloseDate],

     [Updated on Thursday Date],

     FirstContactDate,

     ProspectIndustry,

     LeadOrigin,

     Interval([Planned CloseDate]-FirstContactDate, 'd') as CloseDays

FROM

(ooxml, embedded labels, table is [Lead List]);

LOAD Item,

     ItemCategory,

     [Product/Equipment]

FROM

(ooxml, embedded labels, table is [Master Data Locked]);

Left Keep(LeadList)

LOAD

     AutoNumberHash128(Item,Franchisee,[Planned CloseDate]) as Key,

     ForecastQty

FROM

(ooxml, embedded labels, table is EquipmentForecast);

Please run this and then go to Table viewer and hover over the 'Key' field in the Forecast table. It will show you something called subset ration. Can you share how much that is?