FieldName filtering

[Mooloocrew]

Hi All

I currently receive account application forms as csv files. These files are dynamic in that if the requester is requesting one or more cards the file will contain the data for these as Card1_Description, Card2_Limit, Card2_Description, Card2_Limit etc. The file is generated from a website. It only includes fields where there are responses (and there is no limit on how many cards can be loaded).

I need to create two files from each. One containing the customer details and another containing card requests.

Ideally I would like to be able to count the number of fields with a fieldname containing 'Card'  and 'Description' to determine how many card records I am dealing with.

Is there any way to achieve this?

[rubenmarin]

Hi, check the crosstable option: https://help.qlik.com/es-ES/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

So you can end with a table with Requester, CardType and Value.

And a load resident from this table can return the number of descriptions, as an example with:

LOAD Requester, Count(CardType) as NumDescriptions Resident CrosstableName Group By Requester Where index(CardType,'Description');

From ther you can use the crosstable table or the table with the count of description to create what you want .

[Mooloocrew]

I agree the crosstable is what I need to be using. The attribute name has to be an exact match though doesn't it? Is there a way to use a "contains" logic in the attribute part?

As there is no limit on how many Cards that can be ordered, I cannot specify the crosstable syntax (because I don't know how many cards I am ordering). This brings me back to the beginning - how do I count the number of instances of Card Description x?

I'm thinking I need to do a looped check for the existence of the fields using a counter. unless there is a better way to do this?

Account Name	The Jones Family
Address	123 Fake Road, Jonesville
Card Description1	Joe Jones
Card Limit1	$500
Card Description2	Sue Jones
Card Limit2	$1000
Card Description3	Johnny Jones
Card Limit3	$800

[rubenmarin]

Hi, the Index() funtion used in my last post is like a 'contains', there is also a substring() function.

The crosstable syntax can be used with a "crosstable (...) LOAD * FROM...", it would work if the qulifier fields are the same in all files (or there is no qualifier fields).

In case of no qualifier fields in the file you can create your own using FileName() to retrieve the qualifier field from the file name.