topic Help with crosstable script in App Development

Help with crosstable script

josemaria_cabre — Sat, 16 Nov 2024 18:25:09 GMT

Hi,

I need help with load script, to convert this table:

Type	Profile	Code	Period	01/01/19	01/02/19
H	A	110	Hi	3776	1908,7
H	B	110	Hi	1562	1273
J	C	105	Low	1400	1500

Into this one:

Type	Profile	Code	Period	month	year	qtty
H	A	110	Hi	january	2019	3776
H	B	110	Hi	january	2019	1562
H	A	110	Hi	february	2019	1908,7
H	B	110	Hi	february	2019	1273
J	C	105	Low	january	2019	1400
J	C	105	Low	february	2019	1500

How can I acchieve this using crosstable function in script? I'm getting wrong values when I sum(value) with my script code:

[table1]: CrossTable(dimension, value) LOAD [Type], [43466] as january, [43467] as february FROM [lib://Desktop/file.xlsx] (ooxml, embedded labels, table is data); [table2]: LOAD [Type], [Profile], [Code], [Period] FROM [lib://Desktop/file.xlsx] (ooxml, embedded labels, table is data);

Thanks in advance for your help,

Jose.

Re: Help with crosstable script

raji6763 — Thu, 02 Jul 2020 08:33:32 GMT

hi @josemaria_cabre

here is the solution

convert into cross table
format the date
generate month,year from date field using month(), year() function.

script:

originalTable:
CrossTable(Date,qty,4)
LOAD
"Type",
Profile,
Code,
Period,
num("43466") as [1/1/2019],
num( "43467") as [1/2/2019]
FROM [lib://DataFiles/Crosstabqlikcommunity.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

//-----Solution-----------
loadintoone:
load * resident originalTable;
drop table originalTable;

noconcatenate
output: //get month ,year from date field

load *,
month(Date) as Month,
year(Date) as Year;
load Type,
Profile,
Code,
Period,
date(Date#((Date) ,'DD/MM/YY'),'DD/MM/YYYY') as Date,
qty
resident loadintoone;
drop table loadintoone;

drop field Date; // if you want to drop Date field

regards,

raji

Re: Help with crosstable script

Taoufiq_Zarra — Thu, 02 Jul 2020 08:39:58 GMT

One solution :

Table1: CrossTable(Date, Data, 4) LOAD * INLINE [ Type, Profile, Code, Period, 01/01/19, 01/02/19 H, A, 110, Hi, 3776, "1908,7" H, B, 110, Hi, 1562, 1273 J, C, 105, Low, 1400, 1500 ]; table2: noconcatenate load Type, Profile, Code, Period, Month(Date#(Date,'DD/MM/YYYY')) as month, Year(Date#(Date,'DD/MM/YYYY')) as year,Data as qtty resident Table1; drop table Table1;

output :

Re: Help with crosstable script

josemaria_cabre — Thu, 02 Jul 2020 09:39:49 GMT

Hi!

Both solutions worked perfect, thanks a lot for your help Raji and Taoufiq!

I was wondering why are you using value '4' inside the Crosstable parameters, and the purpose for the noconcatenate prefix on table2.

Best regards,

Jose

Re: Help with crosstable script

Taoufiq_Zarra — Thu, 02 Jul 2020 09:46:31 GMT

1-why 4 :

CrossTable starts "crossing" from the 4 column, i.e after :

Type, Profile, Code, Period

2-why noconcatenate

the tables with the same column name concatenate automatically, so we use Noconcatenate to avoid concatenation and to avoid deleting all the data if we do drop table1.

Re: Help with crosstable script

raji6763 — Thu, 02 Jul 2020 10:24:25 GMT

finally you got answer .😊..please close this thread to click on Accept as a solution.