topic Re: Problem with crosstable in App Development

Problem with crosstable

ecabanas — Sat, 16 Nov 2024 19:34:12 GMT

Hi,

I'm triying to transform this table

customer	SalesId	Brand	Revenues
Customer1	PV001	X	10 €
Customer1	PV001	Y	10 €
Customer1	PV001	Z	10 €
Customer2	PV002	X	10 €
Customer2	PV002	Y	10 €
Customer2	PV002	Z	10 €
Customer1	PV003	X	10 €
Customer1	PV003	Y	10 €
Customer2	PV004	Y	10 €
Customer2	PV004	Z	10 €

into this

Customer	SalesID	BrandX	BrandY	BrandZ
Customer1	PV001	10	10	10
Customer2	PV002	10	10	10
Customer3	PV003	10	10
Customer4	PV004			10

I'm using a Crosstable function but I did not reach the second table as I want. Any idea of how to do it? I'm completely lost

Cross754: CrossTable (Brand,Revenues) LOAD SL_Customer,SalesId resident SaleslineCounter2;

Thank's

Re: Problem with crosstable

Stoyan_Terziev — Thu, 28 Nov 2019 09:31:38 GMT

Hi Ecabanas,

You are looking for the opposite of a crosstable. It's called Generic Load.

Here is a nice article about it:
https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470

I hope that helps!

Regards,

S.T.

Re: Problem with crosstable

ecabanas — Thu, 28 Nov 2019 10:38:05 GMT

Hi @Stoyan_Terziev

Thank's for your help, But i don't want thousands of tables (it's a nightmare 😉 ), I would like to do only one table...how do I join all tables created?

Thank's

Re: Problem with crosstable

Stoyan_Terziev — Thu, 28 Nov 2019 10:43:56 GMT

Hi @ecabanas ,

There is a section in the article dedicated to this part:

Set vListOfTables = ; For vTableNo = 0 to NoOfTables() Let vTableName = TableName($(vTableNo)) ; If Subfield(vTableName,'.',1)='GenericLabel' Then Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ; End If Next vTableNo CombinedGenericTable: Load distinct Key From GenericDB; For each vTableName in $(vListOfTables) Left Join (CombinedGenericTable) Load * Resident [$(vTableName)]; Drop Table [$(vTableName)]; Next vTableName

That's the general script, but you need to adapt it to your case.

This will basically join all those tables back together and form one single big table as you wish to see it.

Kind regards,

S.T.

Re: Problem with crosstable

ecabanas — Thu, 28 Nov 2019 10:57:43 GMT

Hi @Stoyan_Terziev

I did it and adpted in my script but I have an error

SaleslineCounter: LOAD floor(monthend(CreatedatSalesLine)) as created_at, SalesId_ as SalesId_Segmentation , ItemId, Catlevel2, SL_Brand, SL_Customer, Import_Brut FROM [lib://folder_QVD (ulabox_ecabanas)/SalesLine_DAMM.qvd] (qvd); SaleslineCounter2: Load SL_Customer,SalesId_Segmentation, SL_Brand,1 as countbrand754 resident SaleslineCounter where Catlevel2 ='754'; Cross754: Generic LOAD SalesId_Segmentation, SL_Brand,countbrand754 resident SaleslineCounter2; Set vListOfTables = ; For vTableNo = 0 to NoOfTables() Let vTableName = TableName($(vTableNo)) ; If Subfield(vTableName,'.',1)='Cross754' Then Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ; End If Next vTableNo CombinedGenericTable: Load distinct SalesId_Segmentation From Cross754; For each vTableName in $(vListOfTables) Left Join (CombinedGenericTable) Load * Resident [$(vTableName)]; Drop Table [$(vTableName)]; Next vTableName drop table SaleslineCounter2; drop table SaleslineCounter;

error:

11:54:56 No qualified path for file: ***: CombinedGenericTable: Load distinct SalesId_Segmentation From Cross754

Thank's for your patience

Re: Problem with crosstable

ecabanas — Thu, 28 Nov 2019 11:46:12 GMT

Hi @Stoyan_Terziev

I did it as the document said, but as I don't control the routine I have an error that I did not have any idea how to fix it.

This is my code:

SaleslineCounter: LOAD floor(monthend(CreatedatSalesLine)) as created_at, SalesId_ as SalesId_Segmentation , ItemId, Catlevel2, SL_Brand, SL_Customer, Import_Brut FROM [lib://folder_QVD (ulabox_ecabanas)/SalesLine_DAMM.qvd] (qvd); SaleslineCounter2: Load SL_Customer,SalesId_Segmentation, SL_Brand,1 as countbrand754 resident SaleslineCounter where Catlevel2 ='754'; Cross754: Generic LOAD SalesId_Segmentation, SL_Brand,countbrand754 resident SaleslineCounter2; drop table SaleslineCounter2; drop table SaleslineCounter; Set vListOfTables = ; For vTableNo = 0 to NoOfTables() Let vTableName = TableName($(vTableNo)) ; If Subfield(vTableName,'.',1)='Cross754' Then Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ; End If Next vTableNo CombinedGenericTable: Load distinct SalesId_Segmentation From Cross754; For each vTableName in $(vListOfTables) Left Join (CombinedGenericTable) Load * Resident [$(vTableName)]; Drop Table [$(vTableName)]; Next vTableName

But the error is :

Se ha producido el siguiente error: No qualified path for file: *** El error se ha producido aquí: CombinedGenericTable: Load distinct SalesId_Segmentation From Cross754

Thank's for your enormous patience

Best

Eduard

Re: Problem with crosstable

Stoyan_Terziev — Thu, 28 Nov 2019 12:38:57 GMT

Hi,

The issue was that you use Resident load at the wrong place. I fixed this part:

CombinedGenericTable:
Load distinct SalesId_Segmentation RESIDENT SaleslineCounter2;
drop table SaleslineCounter2;

SaleslineCounter: LOAD floor(monthend(CreatedatSalesLine)) as created_at, SalesId_ as SalesId_Segmentation , ItemId, Catlevel2, SL_Brand, SL_Customer, Import_Brut ; LOAD * INLINE [ SalesId_,ItemId,Catlevel2, SL_Brand,SL_Customer,Import_Brut,CreatedatSalesLine 1,A,754,SL_BR1,Cust_1,Import_A,11/28/2019 1,A,754,SL_BR2,Cust_2,Import_B,11/27/2019 ]; SaleslineCounter2: NoConcatenate Load SL_Customer,SalesId_Segmentation, SL_Brand,1 as countbrand754 resident SaleslineCounter where Catlevel2 ='754'; Cross754: Generic LOAD SalesId_Segmentation, SL_Brand,countbrand754 resident SaleslineCounter2; drop table SaleslineCounter; Set vListOfTables = ; For vTableNo = 0 to NoOfTables() Let vTableName = TableName($(vTableNo)) ; If Subfield(vTableName,'.',1)='Cross754' Then Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ; End If Next vTableNo CombinedGenericTable: Load distinct SalesId_Segmentation RESIDENT SaleslineCounter2; drop table SaleslineCounter2; For each vTableName in $(vListOfTables) Left Join (CombinedGenericTable) Load * Resident [$(vTableName)]; Drop Table [$(vTableName)]; Next vTableName

Re: Problem with crosstable

ecabanas — Thu, 28 Nov 2019 13:49:16 GMT

Hi,

Thank's for your help...but still with problems, I received this error, and no idea what it means

14:46:18 Se ha producido el siguiente error: 14:46:18 Unexpected token: 's', expected one of: ',', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', ... 14:46:18 --- 14:46:18 El error se ha producido aquí: 14:46:18 For each vTableName in 'Cross754.Voll Damm','Cross754.Estrella Damm','Cross754.Estrella de Levante','Cross754.Estrella Galicia','Cross754.Cruzcampo','Cross754.Turia','Cross754.San Miguel','Cross754.Moritz','Cross754.Free Damm','Cross754.INEDIT','Cross754.Rosita','Cross754.Buckler','Cross754.Paulaner','Cross754.A.K. Damm','Cross754.Heineken','Cross754.Amstel','Cross754.Daura','Cross754.Alhambra','Cross754.Keler','Cross754.Guinness','Cross754.BrewDog','Cross754.Cervesa del Montseny','Cross754.Franziskaner','Cross754.Mongozo','Cross754.Mahou','Cross754.Carlsberg','Cross754.La Pirata','Cross754.Stone Brewing CO','Cross754.Domus','Cross754.Flying Monkeys','Cross754.Bbp','Cross754.Damm Lemon','Cross754.Menduiña','Cross754.Barcelona Beer Company','Cross754.Skol','Cross754.Xibeca','Cross754.Budweiser','Cross754.Flensburger','Cross754.Weihenstephaner','Cross754.De Ranke','Cross754.Saaz','Cross754.Beck'>>>>>>s<<<<<<','Cross754.Brugse Zot','Cross754.O'hara's','Cross754.Orval','Cross754.Brouwerij Van Steenberge','Cross754.Coronita','Cross754.Delirium','Cross754.St. Bernardus','Cross754.Trappistes Rochefort','Cross754.Triple Karmeliet','Cross754.Val-Dieu','Cross754.Brooklyn','Cross754.Straffe Hendrik','Cross754.Liefmans','Cross754.Porterhouse','Cross754.Caracole','Cross754.La Brava','Cross754.Desperados','Cross754.Brouwerij Verhaeghe','Cross754.Vic Brewery','Cross754.Brunehaut','Cross754.Petràs','Cross754.Gourmet (Marca Blanca)','Cross754.Brutus','Cross754.Ratpenat Cervesers','Cross754.Mixta','Cross754.Brasserie Lefebvre','Cross754.Brouwerij Huyghe','Cross754.Benediktiner','Cross754.Murphy','Cross754.Daura Märzen','Cross754.Samuel Smith','Cross754.Timmermans','Cross754.Cerveza Mandril','Cross754.Zywiec','Cross754.Affligem','Cross754.John Smiths','Cross754.Victoria','Cross754.Bock Damm','Cross754.Birra Moretti','Cross754.Sapporo','Cross754.Brewery Bosteels','Cross754.Floris','Cross754.Celis White','Cross754.Judas','Cross754.Milana','Cross754.El Oso y el Cuervo','Cross754.Sol','Cross754.L'Anjub','Cross754.Brasserie de l'Abbaye des Rocs','Cross754.Kriek','Cross754.Art Cervesers','Cross754.Kasteelbier','Cross754.Maisel's Weisse','Cross754.Cubanero','Cross754.Carling','Cross754.Asahi Breweries','Cross754.Kirin','Cross754.Brasserie Du Bocq','Cross754.Biir','Cross754.Basqueland Brewing Project','Cross754.B. Plankstetten','Cross754.Riedenburger','Cross754.Brouwerij Malheur','Cross754.Iki','Cross754.Popaire','Cross754.Collective Arts Brewing Limited','Cross754.Oskar Blues Brewery','Cross754.Nickel Brook Brewing Co','Cross754.Mikkeller','Cross754.Brasserie Dunham','Cross754.Chimay','Cross754.Brouwerij der Trappisten van Westmalle','Cross754.Shepherd Neame','Cross754.Brasserie d’Achouffe','Cross754.Camerons Brewery','Cross754.Yellow Cab','Cross754.Charles Wells','Cross754.Leffe','Cross754.Brouwerij Oud Beersel','Cross754.Lindemans','Cross754.Brouwerij Rodenbach','Cross754.Krombacher','Cross754.Cannabis','Cross754.Robinsons Brewery','Cross754.Stella','Cross754.Lowenbrau','Cross754.Guineu','Cross754.Tiger','Cross754.Les Brasseurs De Gayant','Cross754.In Peccatum Craft Beer','Cross754.Radeberger','Cross754.Birra & Blues Artesanal Craft','Cross754.Boston Beer Company','Cross754.Brouwerij der Trappistenabdij De Achelse Kluis','Cross754.Brasserie Dubuisson','Cross754.La Calavera Brewing COOP.','Cross754.Harviestoun','Cross754.Glutenberg Brewing CO','Cross754.Les Trois Mousquetaires','Cross754.Cobeer','Cross754.Reptilian','Cross754.Wierquer','Cross754.Birrificio Baladin','Cross754.DouGall's','Cross754.Newcastle','Cross754.La Virgen','Cross754.Cerveses Ponent','Cross754.Ambar','Cross754.Brasserie De Block','Cross754.OOB Brewery','Cross754.Schofferhofer','Cross754.Althaia Artesana','Cross754.Hecatombe Brewing','Cross754.New Belgium Brewing','Cross754.Illa','Cross754.Wychwood','Cross754.Michelada Malinche','Cross754.Brasserie St-Feuillien','Cross754.John Martin','Cross754.Thornbridge Brewery','Cross754.Sierra Nevada Brewing Co.','Cross754.Mont des Cats','Cross754.Brasserie D'Achouffe','Cross754.Brauerei Schloss Eggenberg','Cross754.Dieu du Ciel','Cross754.Amura','Cross754.Brouwerij Van Honsebrouck','Cross754.Unibroue','Cross754.Sawdust City Brewing Co.','Cross754.Varios','Cross754.Brasserie Dieu Du Ciel','Cross754.Central City','Cross754.Complot IPA','Cross754.Ambar IPA','Cross754.Ambar Roja','Cross754.Malquerida','Cross754.Adlerbrau','Cross754.Brouwerij Het Anker','Cross754.Tibidabo Brewing','Cross754.Almogàver','Cross754.St. Sylvestre','Cross754.Cerveza Madera','Cross754.Pilsner Urquell','Cross754.Clausthaler','Cross754.Grimbergen','Cross754.Praga','Cross754.Kagua','Cross754.Shandy'