CrossTable load on a resident table

[ashish_2511]

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

[karthiksrqv]

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.

[ashish_2511]

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

Thanks!

[karthiksrqv]

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

[rahulpawarb]

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

[Anil_Babu_Samineni]

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.