topic Re: CrossTable load on a resident table in QlikView

CrossTable load on a resident table

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

Dear Community -

Here are my input and output tables, Please suggest the best way to achieve this by using resident load and not crosstable load, I/p table is already loaded into qlikview

I/p Table -

O/p Table -

Part No	S1Stock	S2Stock	S3Stock	S4Stock
400044112	1643	34	-3	12

Thanks in advance

-Sneh

Re: CrossTable load on a resident table

karthiksrqv — Thu, 30 Mar 2017 06:23:09 GMT

Hi,

If you want the display format to same as O/P, then create a pivot table. After you create, drag the expressions to make it display horizontally.

Re: CrossTable load on a resident table

Anil_Babu_Samineni — Thu, 30 Mar 2017 06:23:57 GMT

May be this?

Generic LOAD Part*1 as [Part No], Dept & '' & 'Stock' as Dept, Stock Inline [

Part, Dept, Stock

0400044112, S1, 1643

0400044112, S2, 34

0400044112, S3, -3

0400044112, S4, 12

];

Re: CrossTable load on a resident table

stabben23 — Thu, 30 Mar 2017 06:24:08 GMT

Hi,

the easiest way is to create a pivot and drag and drop the DEPOT_CD Dimension a "top" Dimension.

Re: CrossTable load on a resident table

ashish_2511 — Thu, 30 Mar 2017 07:03:09 GMT

Hi. Thanks!

I'm not looking for a Pivot table as I finally want it to be displayed in a table box along with 15 other fields.

Please suggest a way to achieve this as a standalone table in load script.

Re: CrossTable load on a resident table

ashish_2511 — Thu, 30 Mar 2017 07:03:19 GMT

Hi. Thanks!

I'm not looking for a Pivot table as I finally want it to be displayed in a table box along with 15 other fields.

Please suggest a way to achieve this as a standalone table in load script.

Re: CrossTable load on a resident table

ashish_2511 — Thu, 30 Mar 2017 07:07:12 GMT

Hi Anil.

This is not Working

Re: CrossTable load on a resident table

Anil_Babu_Samineni — Thu, 30 Mar 2017 07:11:35 GMT

When you say, This is not working you should mention the reason. And then I've tested with your data and i did and it showing as you expected in that image

Re: CrossTable load on a resident table

ashish_2511 — Thu, 30 Mar 2017 07:33:29 GMT

Hi Anil

Sorry for not mentioning the reason. This is fine but since this creates 4 separate tables in Data model, How do I join this with some other table? or how'd I store this to a qvd?

Thanks!

Re: CrossTable load on a resident table

Thu, 30 Mar 2017 09:06:53 GMT

check out the revers cross table option:

http://community.qlik.com/message/78337#78337

kr, Norbert

Re: CrossTable load on a resident table

karthiksrqv — Thu, 30 Mar 2017 09:08:55 GMT

Hi,

How many Depot values do you think will exist?

If there will be more than 10 or so, then the following approach is not optimal. If the number of values is less, you can do it this way

1. Fetch a unique list of Depot values

2. In a For loop, fetch each value

3. Read the table with the key fields and one Depot value

4. Left Join to a new table with the Depot value as the column name.

Re: CrossTable load on a resident table

ashish_2511 — Thu, 30 Mar 2017 09:22:20 GMT

Hi. There are only 4 depot codes. Could you please post a sample script?

Thanks!

Re: CrossTable load on a resident table

karthiksrqv — Thu, 30 Mar 2017 09:37:10 GMT

Try this:

//Start of sample script

data:

load * Inline [

PartNo,DEPOT_CD,STOCK_QTY

1,s1,1643

2,s2,34

3,s3,-3

4,s4,12

];

data_temp:

Load Distinct

DEPOT_CD as DEPOT_CD_Temp

Resident

data;

For i = 0 to (NoOfRows('data_temp')-1)

Let v_depot_name = Peek('DEPOT_CD_Temp',$(i),'data_temp');

if($(i)=0) then

new_data:

Load distinct

PartNo

resident

data;

endif

left join (new_data)

Load

PartNo,

DEPOT_CD as [$(v_depot_name)]

resident

data

where DEPOT_CD = '$(v_depot_name)';

Next i

Drop table data;

//End of sample script

Re: CrossTable load on a resident table

rahulpawarb — Fri, 31 Mar 2017 04:26:38 GMT

Hello Snehasis,

Trust that you are doing well!

Please refer given draft version of script:

Data:
LOAD *, RecNo() AS RecNo INLINE [
Part No, DEPOT_CD, STOCK_QTY
0400044112, S1, 1643
0400044112, S2, 34
0400044112, S3, -3
0400044112, S4, 12
];


tmpData:
LOAD [Part No], STOCK_QTY AS S1Stock
Resident Data
Where RecNo = 1;


For i=2 to NoOfRows('Data')
JOIN (tmpData)
LOAD [Part No], STOCK_QTY AS S$(i)Stock
Resident Data
Where RecNo = $(i);
Next i

Also refer the sample application attached herewith.

Regards!

Rahul

Re: CrossTable load on a resident table

Anil_Babu_Samineni — Fri, 31 Mar 2017 04:45:56 GMT

I am not sure, Why you want to join or show as Single fact over here. In fact, The power of generic can split into single dimension table in DM. So then they came to understand of fact which return to Which schema is that.(May be look rahulpawarbreply)

For your second question, we can store the same into qvd.