topic Re: straight table in QlikView

straight table

srujanaponnuru — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

i have a source like this in excel.

Manager names
Carry, Jim
Pitt, Brad
Watson, James
Rao, Subba

i need a straight table in qlikview like the below as shown:

Primary	Jim
Secondary	Carry

please suggest how to do.

Re: straight table

jonathandienst — Wed, 26 Oct 2016 05:06:14 GMT

If the question is how to split the names, then something like:

=SubField([Manager Name], ',', 1) // first name/Primary

=SubField([Manager Name], ',', 2) // second name

=Trim(SubField([Manager Name], ',', 2))

Re: straight table

srujanaponnuru — Wed, 26 Oct 2016 05:21:32 GMT

actually my source was like this..sorry for the inconvenience caused.

Manager Names
Carry, Jim Pinto, Noel
Pitt, Brad Carry, Jim
Watson, James Pinto, Noel

Manager Names

Carry, Jim

Pinto, Noel

Pitt, Brad

Carry, Jim

Watson, James

Pinto, Noel

i need an output like this in qlikview

Primary	Jim Carry
Secondary	Noel Pinto

please hlep

Re: straight table

srujanaponnuru — Wed, 26 Oct 2016 05:22:07 GMT

actually my source was like this..sorry for the inconvenience caused.

Manager Names
Carry, Jim Pinto, Noel
Pitt, Brad Carry, Jim
Watson, James Pinto, Noel

Carry, Jim

Pinto, Noel

Pitt, Brad

Carry, Jim

Watson, James

Pinto, Noel

i need an output like this in qlikview

Primary name	Jim Carry
Secondary name	Noel Pinto

please help

Re: straight table

Anil_Babu_Samineni — Wed, 26 Oct 2016 06:01:07 GMT

Can you please share the Excel

Re: straight table

leni_harishwara — Wed, 26 Oct 2016 07:03:38 GMT

Hi,

Please try with the below code,

Tbl:

LOAD ManagerName,

SubField(ManagerName,';',1) as Primary,

SubField(ManagerName,';',2) as Secondary;

LOAD Replace(ManagerName,chr(10),';') as ManagerName

FROM

TestData.xlsx

(ooxml, embedded labels, table is Sheet1);

Assumed, the space between values in the same cell as "enter key value" and provided the code.

Replace(ManagerName,chr(10),';') - This will replace the "enter key" which chr(10) with ";" so the data will get modified to "Carry, Jim;Pinto, Noel" and using Subfield we can take the Primary and Secondary values as in screenshot below,

Regards,

Leni

Re: straight table

HirisH_V7 — Wed, 26 Oct 2016 07:18:42 GMT

Hi,

Is this what you're looking for,

By using this below script,Check this,

Temp:
LOAD *,
If(Right(RowNo()/2,2)<>'.5',[Manager Names] &':' & Peek([Manager Names])) as New
 INLINE [
    Manager Names
    "Carry, Jim"
    "Pinto, Noel"
    "Pitt, Brad"
    "Carry, Jim"
    "Watson, James"
    "Pinto, Noel"
];
Data:
Load RowNo() as ID,
     SubField(SubField(New,':',2),',',2)&' '&SubField(SubField(New,':',2),',',1) as Primary_Name,
    SubField(SubField(New,':',1),',',2)&' '&SubField(SubField(New,':',1),',',1) as Secondary_Name
    


Resident Temp Where New<>Null();
Drop Table Temp;

Hope this Helps,

else Can u please elaborate your'e requirement, with providing much more info.

PFA,

Hirish

Re: straight table

maniram23 — Wed, 26 Oct 2016 07:35:57 GMT

Hi,

This is helpful for you.

please check it.

Re: straight table

srujanaponnuru — Wed, 26 Oct 2016 09:34:30 GMT

thank you harish, this ight help. But only one thing is i dont need id numbers in the table. i just need primary name and secondary name. but inline wil not help for me as i have huge rows of data.

please suggest.

Re: straight table

susovan — Wed, 26 Oct 2016 09:48:16 GMT

Hi Srujana

Try this,

=Replace([Manager names],',',' ')

Re: straight table

HirisH_V7 — Wed, 26 Oct 2016 10:05:51 GMT

Please post some sample data, it will be helpful to look more further. yup! you can exclude 'ID' field from your'e Straight table.

-Hirish

Re: straight table

srujanaponnuru — Wed, 26 Oct 2016 10:15:44 GMT

Manager names	Application name
Pinto, Noel	abs
Patrick, Marie Jim, carry	dfe
Subba, Rao Noel, Pinto	gff
Brad, Pitt Jim, Carry	gtr
Pinto, Noel	eert
Patrick, Marie Jim, carry	dfesdf
Subba, Rao Noel, Pinto	dfesdfgghj
Brad, Pitt Jim, Carry	v
Pinto, Noel	dfehjilp
Patrick, Marie Jim, carry	dfe677
Subba, Rao Noel, Pinto	dfe345
Brad, Pitt Jim, Carry	dfe234
Pinto, Noel	dfe908
Patrick, Marie Jim, carry	dfel;p
Subba, Rao Noel, Pinto	dfe89;,
Brad, Pitt Jim, Carry	dfeasd
Pinto, Noel	dfeqwe
Patrick, Marie Jim, carry	dfezxc
Subba, Rao Noel, Pinto	dfe23415678
Brad, Pitt Jim, Carry	dfe000

i created a search box in qlikview for application name. so when i click any application in search i need to get the table as shown:

say for example if i search an application "dfe" i need to get like this:

Primary name	Marie Patrick
Secondary name	Jim Carry

please help

Re: straight table

HirisH_V7 — Wed, 26 Oct 2016 10:56:49 GMT

Is it like every app will have their ,Primary and secondary names or what , as i can see from the above data its not like that.

Please provide data in excel sheet,it will be helpful to look for clear mapping.

Re: straight table

srujanaponnuru — Wed, 26 Oct 2016 11:09:28 GMT

Manager names	Application name
Pinto, Noel	abs
Patrick, Marie Jim, carry	dfesdf
Subba, Rao Noel, Pinto	gf
Brad, Pitt Jim, Carry	gt
Pinto, Noel	e
Patrick, Marie Jim, carry	dfe123
Subba, Rao Noel, Pinto	dfe456
Brad, Pitt Jim, Carry	v234
Pinto, Noel	dfe567
Patrick, Marie Jim, carry	dfe789
Subba, Rao Noel, Pinto	dfetyuh
Brad, Pitt Jim, Carry	dfenmj
Pinto, Noel	dfecvf
Patrick, Marie Jim, carry	dfedftr
Subba, Rao Noel, Pinto	dfesde
Brad, Pitt Jim, Carry	dfegtyu
Pinto, Noel	dfeuoi
Patrick, Marie Jim, carry	dfeawse
Subba, Rao Noel, Pinto	dfenmjyd
Brad, Pitt Jim, Carry	dfe12wsd

Re: straight table

srujanaponnuru — Wed, 26 Oct 2016 11:10:50 GMT

sorry cant give in excel. each application has two names.

when i search on application , the table should give me primary name as the first in that cell, and the other as secondary from the same cell.

Re: straight table

HirisH_V7 — Wed, 26 Oct 2016 11:19:23 GMT

Check the very first one, has only one name.

Re: straight table

srujanaponnuru — Wed, 26 Oct 2016 11:27:08 GMT

which ever application name has one name , secondary name will be null.

Re: straight table

HirisH_V7 — Wed, 26 Oct 2016 13:49:40 GMT

Hi,

Check this,By using the provided data,In front end by selecting 'application name' you can look into 'primary and secondary' managers.

At Script ,By Creating a loop for each 'Application Name' with the [Manager names] column,

Temp:
LOAD [Manager names],
     If(Isnull([Application name]),Peek([Application name]),[Application name]) as Appname,
     [Application name]
FROM
[Manger Names and apps.xlsx]
(ooxml, embedded labels, table is Sheet1);


Data:
NoConcatenate
Load [Manager names],
     Appname
Resident Temp;
Drop Table Temp;


For each Appname in New
Concatenate
New:
load
Concat([Manager names],':') as [New Manager names],
Appname
resident Data
Group by Appname;


Next;


Final_Table:
NoConcatenate
Load Appname,
     [New Manager names]
Resident Data Where [New Manager names]<>Null();


Drop Table Data;

HTH,

PFA,

Hirish