Merging Fields

[#1]

Hi

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.

[Gysbert_Wassenaar]

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;

[#3]

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

[#4]

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);

[Gysbert_Wassenaar]

See this blog post: The Crosstable Load

[#6]

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

[#7]

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.

[#8]

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

[#9]

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;

[#10]

Hi Krishnama

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