topic Re: Merging Fields in QlikView

Merging Fields

Thu, 19 Mar 2015 09:38:36 GMT

I have a spreadsheet that I have brought into QlikView. It is part of a larger report but it is not affected by the other elements in that report. I want to be able to merge the 3 fields User1, User2 and User 3 to give me a list of users but I also want to be able to give a count of how many times that user has occurred.

My intention is to show this information in a table of some sort such as below:

User	Pick Errors
Ryan	3
Danny	8
Brett	3
Jacob	6
Ian	4

I have attached the qvw.

Re: Merging Fields

Gysbert_Wassenaar — Thu, 19 Mar 2015 10:00:30 GMT

Perhaps you can use the CrossTable function:

PickErrors:

CrossTable(Dummy, Name,10)

LOAD Date as PickErrorDate,

Year(Date) as PEYear,

Month(Date) as PEMonth,

Week(Date) as PEWeek,

[Order No] as OrderNo,

[Ref No] as RefNo,

Code,

Description,

Qty,

[Up/Down],

User1,

User2,

User3

FROM

(ooxml, embedded labels, table is Sheet1);

Drop Field Dummy;

Re: Merging Fields

Thu, 19 Mar 2015 10:12:56 GMT

Hi Gysbert

That's great thanks, works a treat. Could you explain what that does as I don't understand how that has linked the 3 fields.

Thanks Steve

Re: Merging Fields

Thu, 19 Mar 2015 10:20:49 GMT

Hi,

you can try this script,

PickErrors:

LOAD Date as PickErrorDate,

Year(Date) as PEYear,

Month(Date) as PEMonth,

Week(Date) as PEWeek,

[Order No] as OrderNo,

[Ref No] as RefNo,

Code,

Description,

Qty,

[Up/Down],

Text(User1 &'|'& User2 &'|'& User3) as User

// User1,

// User2,

// User3

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Merging Fields

Gysbert_Wassenaar — Thu, 19 Mar 2015 10:32:37 GMT

See this blog post: The Crosstable Load

Re: Merging Fields

Thu, 19 Mar 2015 10:41:54 GMT

Thanks Gysbert, I think this will need a couple of reads before I fully understand it.

Re: Merging Fields

Thu, 19 Mar 2015 12:52:57 GMT

Hi Krishnama

This has actually worked better as it has not messed up the figures. Ideally I would want an individual list of the users to count up the amount of times they have made an error. Using Gysbert's solution corrupted this information. I have attached the qvw that is the complete report. The items in question have the orange borders.

Because the users doing the picking work in teams and not always the same team I want to be able to identify which users are the repeat offenders. When I tried Gysbert's solution this corrupted the information in the text boxes.

Thanks Steve.

Re: Merging Fields

Thu, 19 Mar 2015 12:56:00 GMT

Hi Gysbert

This did work to an extent but then on closer inspection it has changed all the figures in my text boxes.

Because all the users work in teams and not always the same team I need to identify the repeat offenders who are making the pick errors.

Your solution gave me that but it corrupted the figures in the text boxes.

I have attached the full report on a reply to Krishnama.

Thanks

Steve

Re: Merging Fields

Thu, 19 Mar 2015 14:25:13 GMT

Hi,

Try like this.

PickErrors:

LOAD Date as PickErrorDate,

Year(Date) as PEYear,

Month(Date) as PEMonth,

Week(Date) as PEWeek,

[Order No] as OrderNo,

[Ref No] as RefNo,

Code,

Description,

Qty,

[Up/Down],

// Text(User1 &'|'& User2 &'|'& User3) as User

User1 as User,

User2,

User3

FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD

User2 as User

Resident PickErrors;

Concatenate

LOAD

User3 as User

Resident PickErrors;

Re: Merging Fields

Thu, 19 Mar 2015 14:34:14 GMT

Hi Krishnama

I get this error when I reload. It is the same for User3 as well.

Re: Merging Fields

awhitfield — Thu, 19 Mar 2015 15:01:23 GMT

Daft question, have you got an Employees table? I can't open the QVW to see!

Andy

Re: Merging Fields

Thu, 19 Mar 2015 15:05:53 GMT

Hi Andy

Nope, I did wonder that, but then some of the things that you guys do in Qlikview has me baffled so I didn't question it.

Steve

Re: Merging Fields

awhitfield — Thu, 19 Mar 2015 15:18:42 GMT

Ha ha, guess that explains the error then

Re: Merging Fields

awhitfield — Thu, 19 Mar 2015 15:22:27 GMT

Hi Steve, can you post the xls from the original Merger Test.qvw?

Cheers

Andy

Re: Merging Fields

Thu, 19 Mar 2015 15:24:25 GMT

Hi Andy

Spreadsheet attached.

Steve

Re: Merging Fields

awhitfield — Fri, 20 Mar 2015 14:03:33 GMT

@Hi Steve,

I've come to the conclusion that the only way to do this would be as per gwassenaar suggestion using a cross table load, however you may need to rename field to stop it interfering from you other data.

Re: Merging Fields

Fri, 20 Mar 2015 14:34:40 GMT

Thanks Andy

I have stopped it dropping a couple of the fields and done a distinct count on those and I am getting the numbers I am expecting.

Thank you for the help.

Steve