Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Combine 2 tables (Spreadsheet & Access Database)

I have 2 separate databases that our different till systems write to and want to combine a table from each of them together on import. The data is a similar structure,one of the tables does not have as many columns as the other so will have to put in blank fields. I also need to put prefixes on some data to differentiate it from the other table.

Table 1 (EZ_TransTable)

TransNo (Add prefix EZ)), TransDate, TransTime, PLUCode(Add Prefix EZ), Quantity, UnitPrice, TotalPrice, Till, PaymentMethod, DateAdded, ChargeID, RemoteNumber, HeadMemberID, HeadGuestID, PersonID, DOB, Age, PostCode, DiscountID, Discount, Discount Total

Table 2 (FID_TransTable)

TransNo (Add Prefix FD), Date (enter in TransDate Field), Time (Enter in TransTime field), ItemNo (Enter in PLUCode field), Qty (Enter in Quantity Field), Val (Enter in UnitPrice field), NO VALUE IN TotalPrice Field, ECR (enter in Till field), NO VALUES in any of the other fields

How would I go about combining the tables together please? I guess this is probably quite straightforward to do but I'm a complete novice sorry - did try to do it in Excel first but exceeded the 1m+ row limit

Thanks for reading.

1 Solution

Accepted Solutions
MVP
MVP

Re: Combine 2 tables (Spreadsheet & Access Database)

The data model to achieve will depend on your full requirements.

Basically, you can link the tables with common key(s) or concatenate them, latter maybe like

EZ_TransTable:

LOAD

TransNo AS EZ_TransNo,

TransDate,

TransTime,

PLUCode as EZ_PLUCode,

Quantity,

UnitPrice,

TotalPrice,

Till,

PaymentMethod,

DateAdded,

ChargeID,

RemoteNumber,

HeadMemberID,

HeadGuestID,

PersonID,

DOB,

Age,

PostCode,

DiscountID,

Discount,

Discount Total

FROM YourEZ_TransTable;

CONCATENATE

LOAD

TransNo as FD_TransNo,

Date asTransDate,

Time AS TransTime,

ItemNo as PLUCode,

Qty AS Quantitiy,

Val AS UnitPrice,

ECR AS Till

FROM YourFID_TransTable;

See also

Get started with developing qlik datamodels

Advanced topics for creating a qlik datamodel

More advanced topics of qlik datamodels

2 Replies
MVP
MVP

Re: Combine 2 tables (Spreadsheet & Access Database)

The data model to achieve will depend on your full requirements.

Basically, you can link the tables with common key(s) or concatenate them, latter maybe like

EZ_TransTable:

LOAD

TransNo AS EZ_TransNo,

TransDate,

TransTime,

PLUCode as EZ_PLUCode,

Quantity,

UnitPrice,

TotalPrice,

Till,

PaymentMethod,

DateAdded,

ChargeID,

RemoteNumber,

HeadMemberID,

HeadGuestID,

PersonID,

DOB,

Age,

PostCode,

DiscountID,

Discount,

Discount Total

FROM YourEZ_TransTable;

CONCATENATE

LOAD

TransNo as FD_TransNo,

Date asTransDate,

Time AS TransTime,

ItemNo as PLUCode,

Qty AS Quantitiy,

Val AS UnitPrice,

ECR AS Till

FROM YourFID_TransTable;

See also

Get started with developing qlik datamodels

Advanced topics for creating a qlik datamodel

More advanced topics of qlik datamodels

Not applicable

Re: Combine 2 tables (Spreadsheet & Access Database)

Thanks Swuehl, managed to link them with common keys and seems to be working great.

Thanks again.

Community Browser