Converting 500 date Column to into Rows of Date fields in Qliksense using Cross table

[SK28]

Hi,

I have a sample data as Shown below, some are in Year_type_name format and Some are in Year_Month_type_format  here as show below and there are approximately 800 columns with different formats some are in number some are in Percentage, Can anyone help me with this please, Appreciate your help

region	Area	district	2023_rsa1	2023_bsa1	2023m1	2023m2	so on till 2030m12	2023m1_rsa1	so on till 2030m12_Bsa1
South	AA	A	12	12%	5	3	2	1%	1234
North	BB	B	10	13%	3	2	1	2%	324
East	CC	C	9	14%	4	0	3	2%	213
West	DD	D	8	12%	1	-	5	3%	122

in the above Example we have Functions like RSA1, BSA1 like that I have more than 10+ for  8 year data in columns and their summaries 2023_BSA, 2024_BSA, 2025_BSA so on and so forth for all the different functions

I want to do that this using Cross table can anyone help me with this please

Required Output:

Region Area District Function Year Yearmonth(YYYYMM) Measure_values


how to get this all functions in one single column and their details in number please there are approximately 800+ columns 

is that feasible to get Measure_values in single column or multipl measure_colums

Can anyone please assist?

Appreciate your help?

[Rohan]

Hi @SK28 ,

Use the following script & check if it works for you. 

T:
Load * From YourData;

for j = 2023 to 2030

for i = 1 to 12

NewT:
Load
region,
Area,
district,
'$(j)_rsa$(i)' as month,
[$(j)_rsa$(i)] as particular
Resident T;
Load
region,
Area,
district,
'$(j)_bsa$(i)' as month,
[$(j)_bsa$(i)] as particular
Resident T;
Load
region,
Area,
district,
'$(j)m$(i)' as month,
[$(j)m$(i)] as particular
Resident T;
next i;
next j;

drop table T;

exit script;

 Later you can use Date#() & purgechar() to get the Yearmonth(YYYYMM) format.

Let me know if it worked for you.

Regards,

Rohan.

[SK28]

Thanks for the response @Rohan 

But the problem is I have 800+ columns I lf I concatenate It will become and appx 23 particular type, I have to append the script 23 times

Can't be this done in another way? And in 2025 next year, the the 2023 data has to be removed automatically?

[Rohan · Accepted solution]

Hi @SK28 ,

You can straight away use Crosstable, Something like :

T :

Crosstable(Month, Value,3)
Load 

region,
Area,
district,

Month1,

Month2,.....

From Data;

Here once you remove your columns, they will be removed from the data model.

Regards,

Rohan.

[SK28]

I have done that, but it's taking too much time to load the data, hence searching for alter method.
and 

fields names	year	yyyymm	type
2023m2_%_RSA	2023	202302	%_RSA
2023	2023	-	-
2024m1	2024	202401	-
2024m2	2024	202402
2024m12_PSV_RS	2024	202412	PSV_RS

all the fields name I have got into 1 column, I want that to be broken as shown above can anyone help me with this?