Help with crosstable script

[josemaria_cabre]

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.

[raji6763 · Accepted solution]

hi @josemaria_cabre 

here is the solution 

1. convert into cross table
2. format the date
3. 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

[Taoufiq_Zarra · Accepted solution]

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 :

[josemaria_cabre]

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

[Taoufiq_Zarra]

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.

[raji6763]

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