topic Re: $Syn table causing problems after loading extra table in QlikView

$Syn table causing problems after loading extra table

Wed, 14 Jan 2015 21:00:02 GMT

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

Re: $Syn table causing problems after loading extra table

reshmakala — Wed, 14 Jan 2015 21:03:07 GMT

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

Re: $Syn table causing problems after loading extra table

Wed, 14 Jan 2015 22:30:28 GMT

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

Re: $Syn table causing problems after loading extra table

vinafidalgo — Wed, 14 Jan 2015 23:52:20 GMT

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

Re: $Syn table causing problems after loading extra table

marchoctober — Thu, 15 Jan 2015 00:53:33 GMT

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!

Re: $Syn table causing problems after loading extra table

Thu, 15 Jan 2015 06:03:03 GMT

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.

Re: $Syn table causing problems after loading extra table

Thu, 15 Jan 2015 06:07:47 GMT

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

Re: $Syn table causing problems after loading extra table

Thu, 15 Jan 2015 17:43:58 GMT

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

Re: $Syn table causing problems after loading extra table

Thu, 15 Jan 2015 20:58:32 GMT

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

Re: $Syn table causing problems after loading extra table

Thu, 15 Jan 2015 22:07:05 GMT

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?