topic Re: How do you create a field (& populate it) using a field from a crosstable? in QlikView

How do you create a field (& populate it) using a field from a crosstable?

Anonymous — Fri, 29 Dec 2017 21:15:48 GMT

I apologize for the vague header...

I am trying to create a 'Unique Key' table which will be made up of 2 fields. The two fields I am joining are [Negotiator] and [Full Date] (full date is Month and Year spanning May 2015 to December 2017). In short, I want to create a table with every permutation of name and date. I need the unique key because the relationship between name and date becomes many-to-many as I pull in more data points in - example of what I need:

' John May 2015

John June 2015...

... John December 2017

....

Mike May 2015

Mike June 2015...

... Mike December 2017

....

Jane May 2015

Jane June 2015...

.... Jane December 2017 '

I tried keeping it simple and just combine the two, something like: Negotiator & [Full Date] as [Unique Key]

... but quickly realized QV cannot recognize the field [Negotiator] as a relatable field; something to do with it being created in the crosstable.

Below is an outline of my query:

CrossTable([Negotiator], Data_QA)
LOAD Date as [Full Date],
[John Doe],
[Mike Doe],
[Jane Doe],

- (there are about 40 names) -

Hope this is enough information to understand the issue. Any guidance / suggestions on how to create this joined unique key field?

Re: How do you create a field (& populate it) using a field from a crosstable?

pradosh_thakur — Fri, 29 Dec 2017 21:40:07 GMT

can you provide a screenshot of table viewer? for this table may be

Re: How do you create a field (& populate it) using a field from a crosstable?

Anonymous — Fri, 29 Dec 2017 21:47:38 GMT

Yes I can. Although, not sure if it'll help much being the links are many-to-many currently.

Re: How do you create a field (& populate it) using a field from a crosstable?

Anonymous — Fri, 29 Dec 2017 22:21:53 GMT

In a different direction ...

Instead of creating the Unique Key variable in Qlikview do you think it makes sense to create the Unique Key table in excel with all possible permutations of Negotiator and Full Date first. Then loading in both Data_QA and Data_MA from their respective tables WHERE Negotiator & Full Date = [Unique Key]. I guess I would still run into the issue of having to pull either Negotiator or Full Date from the a crosstable, which QV can't do.

Here is a screenshot of an excel sheet outlining the thought...

Thanks for the help as well.

Re: How do you create a field (& populate it) using a field from a crosstable?

pradosh_thakur — Sat, 30 Dec 2017 01:53:18 GMT

Hi Schyler

first of all we can do that in qlikview . can you post the excel with just a few line of dummy data or raw data and how you want to see it .I am sure we will be able to help you . If i ask you to do things without understanding that data and structure it will be a shot in the dark

Re: How do you create a field (& populate it) using a field from a crosstable?

RadovanOresky — Sat, 30 Dec 2017 09:57:20 GMT

Hi Schyler,

I believe you need to create a Link table by joining all possible Negotiators with all possible Dates. Something like this:

LinkTable:

load distinct Negotiator resident QA_Twist; // or any table that has a list of all Negotiators

join (LinkTable) load distinct [Date Key];

Then you can create a composite key, like you mentioned, e.g. Negotiator & [Full Date] as [Link Key]. You will have to create it in this new Link table as well as in any other tables you wish to associate.

You will have to re-load the table, which was created by CrossTable statement, in order to create a composite key in it. Something like:

noconcatenate load

Negotiator & [Full Date] as [Link Key],

Data_QA

resident QA_Twist;

drop table QA_Twist;

Hope these suggestions will help.

Radovan

Re: How do you create a field (& populate it) using a field from a crosstable?

RadovanOresky — Sat, 30 Dec 2017 10:05:13 GMT

Sorry, made a small mistake in the Link table script.

LinkTable:

load distinct Negotiator resident QA_Twist; // or any table that has a list of all Negotiators

join (LinkTable) load distinct [Full Date] resident [Date Key];

Radovan

Re: How do you create a field (& populate it) using a field from a crosstable?

Anonymous — Tue, 02 Jan 2018 17:26:32 GMT

I will attach a doc with my sample Department Key, Date Key, QA (Original), QA_Twist, MA Table, and MA_Twist.

Really the main issue is that I cannot figure out how to call on the field that is created in a cross table. If I use the data from QA that is the data field. If QA_Twist that is the Negotiator field, and similarly for MA & MA_Twist.

Re: How do you create a field (& populate it) using a field from a crosstable?

Anonymous — Tue, 02 Jan 2018 17:40:57 GMT

Here is my sample data..

Re: How do you create a field (& populate it) using a field from a crosstable?

Anonymous — Tue, 02 Jan 2018 17:48:43 GMT

This helps me understand, thank you! Although, I still run into the issue of pulling a loaded field from a cross table.

I agree, it is necessary to create the LinkTable in order to get all possible combinations of name and date.

In this case, it is the field Negotiator that will not pull from QA_Twist.

Any thoughts?

Also see that attached sample data on an above comment to see my data format.

Re: How do you create a field (& populate it) using a field from a crosstable?

RadovanOresky — Wed, 03 Jan 2018 07:22:11 GMT

Hi Schyler,

I looked at the sample data and here's what I would do:

t_QA:

crosstable ("Full Date", Data_QA, 1) load *

FROM [lib://Downloads/Sample Data.xlsx]

(ooxml, embedded labels, table is QA);

QA:

noconcatenate load

Negotiator &''& "Full Date" as %Link_key,

Data_QA

resident t_QA;

LinkTable:

noconcatenate load distinct

Negotiator &''& "Full Date" as %Link_key,

Negotiator,

"Full Date"

resident t_QA;

drop table t_QA ;

You should get something like this:

Do the same for the "MA_Table", but use Concatenate or Join to add the records from MA table to the LinkTable.

concatenate (LinkTable) load distinct

Negotiator &''& "Full Date" as %Link_key,

Negotiator,

"Full Date"

resident t_MA;

This is basically how to construct a LinkTable data structure. But, I'm not that sure if you really need it. I think that you could just concatenate the QA and MA tables (after you did the intitial CrossTable load) into one fact table.

Anyway, hope this helps.

Radovan

Re: How do you create a field (& populate it) using a field from a crosstable?

Anonymous — Thu, 04 Jan 2018 21:18:56 GMT

So simple once its all together.

Thank you for the help!