topic Re: New Table from 3 filtered CSV files (Transpose - Line by Line) in QlikView

New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Wed, 29 Nov 2023 14:56:05 GMT

Hello,

brand new Qlik User with a massive project ...

Reinvent a running application using QLIK instead of hardcoded C# . We do have this hardcoded C# exe file which forwards 3 CSV files line by line into a new SQL Master-table. Applying filters deleting empty rows and transpose the data structure. Acctually the transpose function is the major reason we do this line by line.

Now how will I be able to create this master table in Qlik. The attached picture is just a sample of the first part of the original code - creating the Mastertable from filterd Project table. Transposing is not shown yet. But I need to know if it is possible at all.

In Qlik I hardly codes anything yet. BUT in fact what I did so far is not even working out . The where statement is not considered and the table PROJEKTE looks very poor compared to the original CSV there is a lot of data missing.

Dont be confused because of the xls file extension .. it is a CSV format delimiter Tab.

Best regards

Timo

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

vincent_ardiet_ — Wed, 29 Nov 2023 15:49:50 GMT

This is not very clear to me, however, if your goal is to transpose a row of data in multiple rows, I don't think you will be able to do it in a single pass on Qlik neither.
Could you share dummy data how what you have in entry and of what you expect to have in output?

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

tabletuner — Wed, 29 Nov 2023 16:55:41 GMT

Maybe you can have a look at the crosstable() function. It is used a lot to transpose tables.

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Mon, 04 Dec 2023 12:26:53 GMT

Thank you for your answer... attached I uploaded 2 screenshoots. First shows replication of the actual SAP datasource as a CSV extract. Within this you'll find for each Order (WBS) several network actions (oper/Act)

--> e.g. 34, 45, 100,70etc...

Second screenshot shows the "final" transposed datatable / mastertable - the network activities are within one row of the corresponding order.

The source "Order" and "Network Activity" is listed in 2 separet CSV Files / eventually 2 Separete Data Base Tables / SAP Transactions. The linking key is the Order Number (part of the first 11digits of WBS (X)+ the Item (part of last 8digits(Y)) ).

Q/0XXXXXXXXX-00YY-142

Hope you'll be able to follow this explanation. And even more looking forward to find a solution.

Best Regards

Timo

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Mon, 04 Dec 2023 12:29:33 GMT

Hi... thank you for this idea. If you check my reply to vincent and follow my description you would tell me again that this crosstable () funktion is suitable for me ?

Where can I study the syntax or have a look on examples ..

Thanks a lot

Regards Timo

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Mon, 04 Dec 2023 13:17:46 GMT

Hi again tabletuner... I checked the crosstable function on the net and even this seems to work perferct to transposte columns in to rows , my problem is completly vise versa. I will need to "filter" and transpose ROW - data into columns . I attached a screenshot trying to explane.. for better understanding. Thanks anyway for the crossfunction hint. Best Regards Timo

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Tue, 05 Dec 2023 12:54:17 GMT

Here I add the code of the transpose part . Will this be possible during data load or is it eventually possible during sheet creation to skript this?

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

marcus_sommer — Tue, 05 Dec 2023 13:54:14 GMT

Also the opposite of a crosstable-statement is available, see: The Generic Load - Qlik Community - 1473470

But AFAIK it's quite rare that such a data-structure is useful and beneficial against "normal" structures especially in a final data-model and the UI. I suggest not to try to transfer a SQL scheme and/or any programming logic 1:1 to Qlik else to be open to the native features of Qlik especially the associative data-model.

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Thu, 07 Dec 2023 09:05:02 GMT

to summerice the problem Statement see here :

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

tabletuner — Thu, 07 Dec 2023 09:36:45 GMT

Timo, you marked the answer of Marcus as solwved. But now you are posting a new post. Is your problem solved or not? If not I will have a look at it.

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Fri, 08 Dec 2023 06:04:46 GMT

Hi Tabletuner , not yet ... still working on a final solution. I postet this picture only to finalise the problem statement. Within other Topics in here we move on to eventually do NOT USE generic load. As Marcus explains this is critical and we believe we will be able to find different solution. But to answer you question - Problem is not yet solved only several ways are seen. Best Regards Thanks Timo

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

tabletuner — Fri, 08 Dec 2023 09:15:12 GMT

Hi Timo, in the attachment you will find a general idea of the use of generic load to your problem. After some finetuning in the transformation you might be able to solve it.

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Fri, 08 Dec 2023 09:40:20 GMT

thanks TableTuner . I need to talk to our companies IT to import as I am probably not allowed yet. I get a timeout and I am not able to see the LOG file. "The Rest API called Timeout between Services"

Best Regards

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Tue, 12 Dec 2023 06:12:12 GMT

Hi Tabletuner - I do still have some issues with this file. Can't import. Even our IT will get the ""The Rest API called Timeout between Services" error. Can you let me know what might be the issue ?

Regards

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

tabletuner — Tue, 12 Dec 2023 06:20:14 GMT

Hi Timo,

the file was created in QlikView. Maybe you are trying to open it in Sense?

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Tue, 12 Dec 2023 06:25:18 GMT

This is true! Please can you supply a Sense version ?

Re: New Table from 3 filtered CSV files (Transpose - Line by Line)

TimoHart — Tue, 12 Dec 2023 06:40:06 GMT

I got it Tabletuner ... we extracted code and moved to Sense ...