topic Re: how to add columns dynamically in straight table? in QlikView

how to add columns dynamically in straight table?

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

Hi,

I have table that consist

Pid, Pname

1 ,mango

2 ,coriander

3 ,pepper

if the pid again comes it needs to same columns beside like following

pid, pname, pid, pname

1 ,mango, 1 , banana

2 ,coriander

3 ,pepper 3 ,sapota

dynamically it needs to add columns to table if the same pid is comimg again and again . how can i achieve please help me.

Re: how to add columns dynamically in straight table?

DavidFoster1 — Tue, 20 Jun 2017 12:49:08 GMT

I dont think this is possible to achieve without using macros. Why are you trying to do this?

Re: how to add columns dynamically in straight table?

vinieme12 — Tue, 20 Jun 2017 12:54:40 GMT

how exactly is pid coming again?

Re: how to add columns dynamically in straight table?

Anil_Babu_Samineni — Tue, 20 Jun 2017 12:58:25 GMT

What was the logic behind? Where you are getting pid again? Will you able to explain little more?

Re: how to add columns dynamically in straight table?

Akina0929 — Tue, 20 Jun 2017 12:59:57 GMT

in the same pid different pnames are there, if the same pid and different pname comes it needs to add separate column to the straight table

Re: how to add columns dynamically in straight table?

Akina0929 — Tue, 20 Jun 2017 13:11:19 GMT

HI Anil,

I have following table,

state code ,dist

111 ,guntur

111 ,prakasam

111 ,ongole

222 ,adilabad

222 ,khammam

333 ,vadi

I want to show above data to like as follows

state code ,dist ,dist ,dist

111 ,guntur ,prakasam ,ongole

222 ,adilabad, khammam

333 ,vadi

is it posssible to show like this please help me

Thanks & Regards

Anjaneyulu

Re: how to add columns dynamically in straight table?

vinieme12 — Tue, 20 Jun 2017 13:25:36 GMT

would this work?

Create a Straight table

Dimension

StateCode

Expression

Concat(Distinct dist,'; ')

Re: how to add columns dynamically in straight table?

Akina0929 — Tue, 20 Jun 2017 13:44:01 GMT

hi vineeth,

not in single column ,it needs to create separate columns

Re: how to add columns dynamically in straight table?

Anil_Babu_Samineni — Tue, 20 Jun 2017 13:58:38 GMT

Haha, That's not possible. Could be possible with Adhoc Reports. Work around may be this?

Re: how to add columns dynamically in straight table?

vishsaggi — Tue, 20 Jun 2017 14:02:50 GMT

Using vineeth's expression try like below:

Table1:

LOAD statecode, subfield(distance, ';',1) AS dist1,

subfield(distance, ';',2) AS dist2,

subfield(distance, ';',3) AS dist3;

LOAD statecode, Concat(dist,';') AS distance

Group By statecode;

LOAD * INLINE [

statecode ,dist

111 , guntur

111 , prakasam

111 , ongole

222 , adilabad

222 , khammam

333 , vadi

];

Re: how to add columns dynamically in straight table?

satheshreddy — Tue, 27 Jun 2017 13:06:15 GMT

Hi Anil,

thanks,

if its have n no of records with multiple dist,tal,mundal how we can achieve.

we need three columns dynamically if values are different.

we will get dynamically columns in this situation?

Regards

Sathish

Re: how to add columns dynamically in straight table?

vishsaggi — Tue, 27 Jun 2017 14:44:24 GMT

May be try this? This will dynamically create the fields based on number of Distinct statecodes.

Table1:

LOAD statecode, Concat(dist,';') AS distance

Group By statecode;

LOAD * INLINE [

statecode ,dist

111 , guntur

111 , prakasam

111 , ongole

222 , adilabad

222 , khammam

333 , vadi

444, Guntur

444, Vadi

444,

555, Ongole

555, Vadi

555, khammam

555, Adilabad

555, Guntur

666, Prakasham

666, Karimnagar

];

ISLAND:

LOAD DISTINCT statecode AS stateCnt

Resident Table1;

LET vCnt = NoOfRows('ISLAND');

For i = 1 to $(vCnt)

NoConcatenate

Table1:

LOAD statecode,

subfield(distance, ';', $(i)) AS dist$(i)

Resident Table1;

Next i ;

Drop Table Table1;

Re: how to add columns dynamically in straight table?

satheshreddy — Wed, 28 Jun 2017 06:27:53 GMT

Hi Vishwarath Nagaraju,

can you share me the qvw file am getting wrong result.

Regards

Sathish

Re: how to add columns dynamically in straight table?

qlikview979 — Wed, 28 Jun 2017 07:09:06 GMT

PFA

Regards

Re: how to add columns dynamically in straight table?

vishsaggi — Wed, 28 Jun 2017 14:25:30 GMT

Here it is.

Re: how to add columns dynamically in straight table?

satheshreddy — Thu, 29 Jun 2017 10:20:27 GMT

Hi Nagaraju,

1).here each dist we are creating one column but if we have 10000 number off rows we will get 10000 columns right?

here for loop will create huge columns.

2). one more thing here i need to check 3 columns and need to provide three separate columns for each record.

Regards

Sathish

Re: how to add columns dynamically in straight table?

Thu, 29 Jun 2017 10:34:49 GMT

for just having three column you can change the for loop counter.

just change $(vCnt) with three in Nagaraju Code.

Re: how to add columns dynamically in straight table?

satheshreddy — Thu, 29 Jun 2017 11:13:10 GMT

Hi Agarwal,

here we are getting lots off tables if records have 1cror what should need to do, we cant create 1 cror tables here right?

i need front end Possibility to create dynamical columns.

Ex: if 1 record have 5 different dist that five need to convert separate columns.

if we have 1 record 1000 different dist how we can we cont store that much tables in data model it will give performance issue surely

Regards

Sathish

Re: how to add columns dynamically in straight table?

vishsaggi — Thu, 29 Jun 2017 12:03:57 GMT

Yes, it will not load properly and can freeze. So what exactly your requirement and why you need like 1000 columns, i am pretty much sure any User would like or have time to view those 1000 columns or how many ever. Picking up the most important columns should be your priority.

However, what is your actual data source and how the data looks can you explain a little more in your requirement?

I am sorry was this your requirement or you just wanted to know what happens if we have 1000 rows of data coming?

Re: how to add columns dynamically in straight table?

satheshreddy — Fri, 30 Jun 2017 05:07:30 GMT

Hi Nagaraju,

my users asking based on one CID they want to see 1 product id as a column like how many products they purchased that all products they want to see in columns and also product weight also separate columns each product wise like this my user requirement.

finally they want like below.

CID PID Pweight PID Pwieght .........

1). here they want dynamically columns generation based on products.

2). they want to compare each CID.

Regards

Sathish.